CODEWITHSUNDEEP
sqlrankpartitionrow-number
Quoc Vo
Quoc Vo

Reputation: 21

Calculate total for last 2 month in SQL

I am a new SQL learner and am practicing Partition, Rank, and Row_Number. I have a table like below (query). I want to calculate last 2 months / Total of that last 2 month.

create table mytable(billing varchar(50), month date, usd int); 
insert into mytable(billing, month, usd) 
values ('CA - N', '2020-01', 56391),
('CA - N', '2020-02', 61220),
('CA - N', '2020-03', 56980),
('CA - N', '2020-04', 62833),
('CA - N', '2020-05', 66521),
('CA - N', '2020-06', 76571),
('CA - N', '2020-07', 75277),
('CA - N', '2020-08', 128640),
('CA - N', '2020-09', 118395),
('CA - N', '2020-10', 79436),
('CA - N', '2020-11', 136632),
('CA - N', '2020-12', 121607),
('CA - N', '2021-01', 3441113),
('CA - N', '2021-02', 3090556),
('CA - N', '2021-03', 3187036),
('CA - N', '2021-04', 3365094),
('CA - N', '2021-05', 3793464),
('CA - N', '2021-06', 3809898),
('CA - S', '2021-01', 425074),
('CA - S', '2021-02', 426753),
('CA - S', '2021-03', 468677),
('CA - S', '2021-04', 442293),
('CA - S', '2021-05', 219375),
('CA - S', '2021-06', 222490),
('NY', '2020-01', 326096),
('NY', '2020-02', 368638),
('NY', '2020-03', 408190),
('NY', '2020-04', 418234),
('NY', '2020-05', 409246),
('NY', '2020-06', 445423),
('NY', '2020-07', 431093),
('NY', '2020-08', 445916),
('NY', '2020-09', 481881),
('NY', '2020-10', 508123),
('NY', '2020-11', 527837),
('NY', '2020-12', 574562),
('NY', '2021-01', 661497),
('NY', '2021-02', 630369),
('NY', '2021-03', 651762),
('NY', '2021-04', 696871),
('NY', '2021-05', 690926),
('NY', '2021-06', 677027),
('NV', '2020-01', 772361),
('NV', '2020-02', 787274),
('NV', '2020-03', 859108),
('NV', '2020-04', 821224),
('NV', '2020-05', 738949),
('NV', '2020-06', 683280),
('NV', '2020-07', 661305),
('NV', '2020-08', 690829),
('NV', '2020-09', 633983),
('NV', '2020-10', 657713),
('NV', '2020-11', 655847),
('NV', '2020-12', 825628),
('NV', '2021-01', 823582),
('NV', '2021-02', 723389),
('NV', '2021-03', 797042),
('NV', '2021-04', 884270),
('NV', '2021-05', 950537),
('NV', '2021-06', 795128);

here is my script to calculate last 2 months

SELECT billing, month as reporting_month,
SUM(SUM(usd)) OVER (PARTITION BY billing ORDER BY month ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS total_last_2m_usd   
FROM mytable
GROUP BY billing, month
ORDER BY billing, month;

From above script, I can have total_last_2m (grouping by billing). Now, I stuck at calculate total of all transaction for that 2 month.

For example, I am in year (2021),

2021-01: total_last_2m_usd is calculated by (2020-11 + 2020-12) (grouping by billing)
2021-02: total_last_2m_usd is calculated by (2020-12 + 2021-01) (grouping by billing)
2021-03: total_last_2m_usd is calculated by (2021-01 + 2021-02) (grouping by billing)
.... below is another column that I want to calculate.
2021-01: total is calculated by (2020-11 + 2020-12) from all billing
2021-02: total is calculated by (2020-12 + 2021-01) from all billing
2021-03: total is calculated by (2021-01 + 2021-02) from all billing
....

Would appreciate if you could help me to solve this.

Thanks

Upvotes: 1

Views: 624

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

In Standard SQL, you could use range with an interval:

SELECT billing, month as reporting_month,
       SUM(SUM(usd)) OVER (PARTITION BY billing ORDER BY month ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS total_last_2m_usd,
       SUM(SUM(usd)) OVER (ORDER BY month RANGE BETWEEN INTERVAL '2 MONTH' PRECEDING AND INTERVAL '1 MONTH' PRECEDING)   
FROM mytable
GROUP BY billing, month
ORDER BY billing, month;

However, not all databases support this standard syntax or might have slightly different syntax for the INTERVAL constants.]

Upvotes: 0

Related Questions