Geek asks and answers

The most common number in a MYSQL SELECT statement

I am trying to get a MYSql instruction to spit out the most common number in a field. I believe that I have to use COUNT(QUANTITY), but I'm confused by that GROUP BYand ORDER BYI can not get the correct MODE (the most common number).

* EDIT *

Here is an example 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 , since it is most often found

+5
source share
4 answers
SELECT QUANTITY,COUNT(*)
FROM ...
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
+4
source 
SELECT ORDER_NUMBER AS ORDER, COUNT(QUANTITY) as numorders
FROM table 
GROUP BY ORDER_NUMBER
ORDER BY numorders
+2
source

to get the top 10 order_numberdo

select order_number, count(order_number) as quantity
from your_table
group by order_number
order by quantity desc
limit 10
+1
source 
SELECT QUANTITY, COUNT(QUANTITY) AS TOTAL_Q
 FROM MYTABLE
 GROUP BY QUANTITY
 ORDER BY TOTAL_Q DESC

this will give you the amount of quanity from most to least ....

0
source

More articles:


All Articles