CODEWITHSUNDEEP
mysql
Nibrane
Nibrane

Reputation: 13

MySQL query with GROUP BY, SUM, MAX and subquery

I have the following tabel structure:

Id  Num1  Num2  Type  Num3
1    2     2     1     4
1    3     1     2     5
1    1     1     3     2
2    2     1     1     3
2    0     1     2     2
2    4     3     3     6

I need a query with group by 'Id', sum of 'Num1', sum of 'Num2', max of 'Num3' and the 'Type' related to the MAX of 'Num3'. So, the desired output is:

Id  Sum(Num1)  Sum(Num2)  type  Max(Num3)
1       6          4       2       5   
2       6          4       3       6

Without this related 'Type' the query below works fine:

SELECT
 Id,
 SUM(Num1),
 SUM(Num2),
 MAX(Num3)
GROUP BY
 Id

I tried different methods of subquery but can't make it work yet.

Upvotes: 1

Views: 53

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522731

Your problem is a bit of a spin on the greatest value per group problem. In this case, we can use a subquery to find the max Num3 value for each Id. But, in the same subquery we also compute the sum aggregates.

SELECT
    t1.Id,
    t2.s1,
    t2.s2,
    t1.Type,
    t1.Num3
FROM yourTable t1
INNER JOIN
(
    SELECT Id, SUM(Num1) AS s1, SUM(Num2) AS s2, MAX(Num3) AS m3
    FROM yourTable
    GROUP BY Id
) t2
    ON t1.Id = t2.Id AND t1.Num3 = t2.m3;

As a hat tip to MySQL 8+, and to ward off evil spirits, we can also write a query using analytic functions:

SELECT Id, s1, s2, Type, Num3
FROM
(
    SELECT
        Id,
        SUM(Num1) OVER (PARTITION BY Id) s1,
        SUM(Num2) OVER (PARTITION BY Id) s2,
        Type,
        Num3,
        MAX(Num3) OVER (PARTITION BY Id) m3
    FROM yourTable
) t
WHERE Num3 = m3;

Upvotes: 2

Related Questions