Search code examples
mysqlselectmode

Most common number in MYSQL SELECT statement

I am trying to get a MYSql statement to spit out the most common number in a field. I believe I am supposed to use COUNT(QUANTITY) but I am confused by which to GROUP BY and ORDER BY, I can't seem to get the correct MODE (Most common number).

*EDIT*

Here is a sample table:

QUANTITY | ORDER_NUMBER
   1         51541
   4         12351
   5         11361
   5         12356
   6         12565
   8         51424
   10        51445
   25        51485

The MYSql statement should spit out the number 5 because it appears most often

Solution 
  • SELECT QUANTITY,COUNT(*)
FROM ...
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;