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 subqueries - Because the above is already placed within one.

---

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.

---

You can not do what you're asking without a subquery, because you can't run two aggregate functions, one on top of the other.

---

Will this work for you?

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

It contains a subquery, but maybe not in the sense you were thinking.

---

This works for me.

select video_id, sum(video_plays) as sum_video_plays
from (
    select video_id, video_plays
         , row_number() over (partition by video_id
                              order by video_id desc) as rn
    from video_statistics
) as T
where rn = 1
group by video_id;

---

can't you just do this?:

SELECT video_id, max(video_plays) AS max_video_plays    
FROM `video_statistics` v_stat    
GROUP BY v_stat.`video_id` ASC

There's an example here:

http://dev.mysql.com/doc/refman/5.1/de/select.html

SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) > 10;

EDIT: second attempt, albeit using a subquery:

select max(sum_video_plays)
from (
SELECT video_id, sum(video_plays) AS sum_video_plays    
FROM `video_statistics` v_stat    
GROUP BY v_stat.`video_id`
) x

Your outer query may well be selecting from a much smaller set, and (depending on your data distribution etc.) may be quite performant.