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
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 |
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;