MySQL - select max value with of sum over year

Question

I have table like:

  v   | day | month | year
  1      1     1      1950
  2      2     1      1950
  3      3     1      1950
  1      1     2      1950
  1      1     2      1951
  2      1     2      1952

I have used this query to select MINs

SELECT SUM(v) AS sum_val, year, month
FROM `d`
GROUP BY year, month

This will result in

sum_val | year | month  
  6       1950     1
  1       1950     2
  1       1951     2 
  2       1952     2

How can I select max of sum_val with assigned year grouped by month? I have tried

SELECT (MAX(f.sum_val)) AS max_sum, f.year, f.month
FROM (
    SELECT SUM(v) AS sum_val, year, month
    FROM `d`
    GROUP BY year, month
) AS f
GROUP BY month

but this incorrectly assign year to value

max_sum | year | month  
   6      1950    1
   2      1950    2   <--- should be 1952

SqlFiddle: http://sqlfiddle.com/#!9/ab23b4/6/0

You could save yourself a world of pain by storing Y-m-d as a single entity — Strawberry, Feb 17 '19 at 09:42

ScaisEdge · Accepted Answer · 2019-02-17T09:14:50.110

2

You could use some subquery baset on sum_val in join

select t2.sum_val, t2.year, t2.month 
from (
    SELECT SUM(v) AS sum_val, year, month
    FROM `d`
    GROUP BY year, month
) t2 
INNER JOIN (
    select max(sum_val) max_val, year
    from (
        SELECT SUM(v) AS sum_val, year, month
        FROM `d`
        GROUP BY year, month
    )  t  
    group by year 
) t3  on t3.max_val = t2.sum_val    and t3.year  = t2.year

NB in ths way if you have two rows that match max val the query return two rows

edited Feb 17 '19 at 09:14

answered Feb 17 '19 at 08:58

ScaisEdge

131,976
10
91
107

1

This gives only 1 year, while it should be grouped by year – user4035 Feb 17 '19 at 09:07

MySQL - select max value with of sum over year

1 Answers1