SQL sum then find max

Question

Here's what I'm trying to achieve. Basically I have a relation in which I have a count for each ID and month. I'd like to sum the counts for each id by month (middle table in the picture) and then from that find the maximum value from all those sums by month, and show the month, id, and the maximum value in that order. Here's what I've got so far:

SELECT month, MAX(summed_counts) AS maximum_result
FROM
 (SELECT month, id, SUM(counts) AS summed_counts
  FROM info WHERE year=2017 GROUP BY month, id)
AS final_result GROUP BY month ORDER BY month ASC;

However as soon as I add id it no longer works:

SELECT month, id, MAX(summed_counts) AS maximum_result
FROM
 (SELECT month, id, SUM(counts) AS summed_counts
  FROM info WHERE year=2017 GROUP BY month, id)
AS final_result GROUP BY month, id ORDER BY month ASC;

Any suggestions?

Add some sample table and the expected result - as well formatted text. — jarlh, May 16 '17 at 09:42
This SELECT works for me in Oracle, if I remove the "AS final_result" after the inner SELECT. What SQL is it? — Galcoholic, May 16 '17 at 09:51
For me I can see it is working I have month, Id and maximum_results — devil_coder, May 16 '17 at 09:55
@devil_coder: Apache Derby is weird and unintuitive. This code just won't work ᕦ(ò_óˇ)ᕤ — , May 16 '17 at 10:00
What does it mean "no longer works"? Do you get an error, or is the result set not what you expect? — Galcoholic, May 16 '17 at 10:01
@Derek yes may be I used SQL Server 2016. Don't have Apache Derby to test sorry — devil_coder, May 16 '17 at 10:02
@Galcoholic: the code is not what I expect. It shows me the same as `SELECT month, id, SUM(counts) AS summed_counts FROM info WHERE year=2017 GROUP BY month, id` which is the sum of counts for each month and each id. — , May 16 '17 at 10:03
@devil_coder You should have, it is part of all JDKs in the "db" folder. — Galcoholic, May 16 '17 at 10:04
I don't understand why someone would vote me down?? I've done everything I've been asked to do: provided a picture, an explanation of the problem, an attempt, answered all follow up questions, and still someone voted me down! — , May 16 '17 at 10:51
Not me... Please, describe with words what result you would like to get, and I will try to adapt your query. For example: get the maximum sum count and the related id per month. — Galcoholic, May 16 '17 at 11:12
I think you should check this question, maybe it helps: [link](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Galcoholic, May 16 '17 at 12:48
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , May 16 '17 at 13:58

score 0 · Answer 1 · answered May 16 '17 at 19:54

Try this (MS SQL):

select distinct month, 

    (select top 1 SUM(counts)
        FROM info info_detail
        WHERE year=2017 and info_detail.month=info.month
        GROUP BY id
        order by SUM(counts) desc
    ) as max_value,

    (select top 1 id 
        FROM info info_detail
        WHERE year=2017 and info_detail.month=info.month
        GROUP BY id
        order by SUM(counts) desc
    ) as max_value_id

    from info 
    where year=2017
    ORDER BY month

Thanks for that unfortunately it shows an error when I try it in Apache. — , May 16 '17 at 23:40

SQL sum then find max

1 Answers1