1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

[SQL] Calculate total for last 2 month in SQL

Discussão em 'Outras Linguagens' iniciado por Stack, Agosto 4, 2021.

  1. Stack

    Stack Membro Participativo

    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

    Continue reading...

Compartilhe esta Página