Friday, 6 September 2013

Getting the highest count from a monthly count query

Getting the highest count from a monthly count query

Dodgy title, I know.
I know I can do this in PHP quite easily, but want to know if it's
possible using my SQL query.
I'm producing a bar chart showing new members to my site.
JAN | FEB | MAR | APR
-----|-------|-------|-------------
101 | 163 | 282 | 233
This is what I have done so far:
SELECT DATE_FORMAT(created, '%Y') as 'year',
DATE_FORMAT(created, '%b') as 'month',
IFNULL(COUNT(user_id),0) as 'total'
FROM users
WHERE DATE_FORMAT(created, %Y) = 2013
GROUP BY DATE_FORMAT(created, '%Y%m')
But to develop a bar graph, I need to know what the highest count was. In
my example above, I'd like to return 282 as the highest count, so I can
calculate the percentage of the bar.
Is this possible and how? I'm thinking maybe I need a sub-query but not
really too sure on the best method for performance.
This is what I'd like to return:
JAN | FEB | MAR | APR | Highest_Count
-----|-------|-------|-------|--------------
101 | 163 | 282 | 233 | 282

No comments:

Post a Comment