BeaversAreDamned BeaversAreDamned - 3 months ago 10
MySQL Question

MAX on columns generated by SUM and GROUP BY

I'm trying to get the MAX on a column which is generated dynamically using the SUM statement. The SUM statement is used together with the 'GROUP by' syntax.

This is the original query, however it needs to be modified to work with grouping, sums and of course MAX.

SELECT SUM(video_plays) AS total_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` ASC


As you can see SUM is adding all the values inside video_plays as *total_video_plays*..

But I SIMPLY want to get the MAX of *total_video_plays*

My attempts are below, however they do not work..

SELECT SUM(video_plays) AS MAX(total_video_plays)
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` ASC


How would you get the MAX on a column made dynamically without using sub quieres - Because the above is already placed within one.

Answer

Something like

SELECT SUM(video_plays) AS total_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` 
ORDER BY total_video_plays DESC 
LIMIT 1 

Hat Tip OMG Ponies for proper MySQL dialect.