pythonsqldatabasesqlite

SQLite 3: Sum a column and also show last value


I have found some similiar question but all have different requierements and cant figure out how to change them to my needs. So I have this table:

Name      Value     Year
------------------------
John      5         2021
John      10        2022
John      6         2023
Kat       20        2021
Kat       10        2022
Kat       12        2023  

Expected result:

Name      Total     LastYear
----------------------------
Kat       42        12
John      21        6

Of course easy part for the first two is:

select name, sum(value) as Total from names group by name order by sum(value) desc

but how am I able to get the last one? I tried union all, subselect (select value from names where year = 2023), nothing comes out how I want it to. ( the 2023 will replaced by a variable ofc)

Thanks for the help


Solution

  • Here are two options in the same sql:

    --      S a m p l e    D a t a :  
    Create Table tbl AS
    Select 'John' as a_name,  5 as a_value,  2021 as a_year Union All
    Select 'John',      10,        2022  Union All
    Select 'John',      6,         2023  Union All
    Select 'Kat',       20,        2021  Union All
    Select 'Kat',       10,        2022  Union All
    Select 'Kat',       12,        2023;
    
    --      S Q L : 
    Select   t.a_name, Sum(t.a_value) as Total,
             LAST_VALUE(t.a_value) Over(Partition By t.a_name Order By t.a_year) as last_year_1, 
             Max(Case When a_year = Cast(StrFTime('%Y', Date('now')) as Int) - 1  Then a_value End) as last_year_2
    From     tbl t
    Group By a_name
    Order By a_name;
    

    R e s u l t :

    a_name Total last_year_1 last_year_2
    John 21 6 6
    Kat 42 12 12

    fiddle

    For some unknown reason the above code works as is - but the LAST_VALUE() without following Max() column gives faulty result. Thanks choroba for noticing and commenting...

    So here are both codes separately that works ok ... ... with just normal aggregation

    Select   t.a_name, Sum(t.a_value) as Total, 
             Max(Case When a_year = Cast(StrFTime('%Y', Date('now')) as Int) - 1  Then a_value End) as last_year_1
    From     tbl t
    Group By a_name
    Order By a_name;
    

    ... OR with just analytic functions (but with Distinct keyword) ...

    Select   Distinct  
             t.a_name, Sum(t.a_value) Over(Partition By t.a_name) as Total,
             LAST_VALUE(t.a_value) Over(Partition By t.a_name Order By t.a_year
                                        Rows Between Unbounded Preceding And Unbounded Following) as last_year_1
    From     tbl t;
    

    fiddle