CODEWITHSUNDEEP
sql
wael
wael

Reputation: 1

i need to sum sale and QTy for each DEP between to dates in 2 different column

  Date      Dep     Sales      Qty
01/07/2018  0103    13,618     459
20/08/2018  0325    237        13
01/01/2018  0318    2,121      39 
30/06/2018  0105    19,886    1,586
01/07/2019  0103    13,618     459
01/04/2019  0325    237        13
01/01/2020  0318    2,121      39
30/06/2020  0105    19,886    1,586

i need to sum sale and QTy for each DEP when date is between (01/07/2018 to 30/06/2019) on a column (18) and date is between (01/07/2019 to 30/06/2020)on anther a column (19)

Dep     Sales 18    Sales 19    Qty 18    Qty 19
0103    13,618       13,618       459      459
0325    237           237         13       13
0318    2,121        2,121        39       39
0105    19,886      19,886      1,586     1,586

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can use conditional aggregation:

select dep,
       sum(case when date >= '2018-01-01' and date < '2019-01-01' then sales else 0 end) as sales_2018,
       sum(case when date >= '2019-01-01' and date < '2020-01-01' then sales else 0 end) as sales_2019,
       sum(case when date >= '2018-01-01' and date < '2019-01-01' then qty else 0 end) as qty_2018,
       sum(case when date >= '2019-01-01' and date < '2020-01-01' then qty else 0 end) as qty_2019
from t
group by dep;

Date operations are notoriously database-dependent. Something like this should work on whatever database you are using.

Upvotes: 1

Related Questions