CODEWITHSUNDEEP
mysqlsql
Prassanth
Prassanth

Reputation: 69

How to find difference in same column applying `group by` in SQL?

I need to find the difference between the points grouping by Id column.

Id | Year | Points
---+------+-------
1  | 2017 | 10
1  | 2018 | 20
2  | 2017 | 13
2  | 2018 | 16
3  | 2017 | 25
3  | 2018 | 20

Expected result:

Id | Points
---+-------
1  | 10
2  | 3
3  | -5

Upvotes: 0

Views: 986

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

do aggregation 

select
id,sum(case when year = 2018 then points end) -sum(case when year = 2017 then points end) as diff
 from tablename group by id

Upvotes: 2

Akina
Akina

Reputation: 42612

SELECT *, 
       points - LAG(points) OVER ( PARTITION BY id
                                   ORDER BY year ) delta_to_prev
FROM sourcetable

PS. Needs MySQL 8+.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want the difference between the years, you don't need group by:

select t2017.id, t2017.points as points_2017,
       t2018.points as points_2018,
       (t2018.points - t2017.points) as diff
from t t2017 join
     t t2018
     on t2017.id = t2018.id and
        t2017.year = 2017 and
        t2018.year = 2018;

You can do something very similar with conditional aggregation:

select id,
       sum(case when year = 2017 then points end) as points_2017,
       sum(case when year = 2018 then points end) as points_2018,
       (sum(case when year = 2018 then points end) -
        sum(case when year = 2017 then points end)
       ) as diff
from t
group by id;

Upvotes: 2

Related Questions