Use different fields based on condition-CodePudding

I log the daily produced energy of my solar panels. Now I want to create a SQL statement to get the sum of produced energy for each month but separate columns for each year.

I came up with the following SQL statement:

SELECT LPAD(extract (month from inverterlogs_summary_daily.bucket)::text, 2, '0') as month,
sum(inverterlogs_summary_daily."EnergyProduced") as  a2022
from inverterlogs_summary_daily
WHERE
  inverterlogs_summary_daily.bucket >= '01.01.2022' and inverterlogs_summary_daily.bucket < '01.01.2023'
group by month
order by 1;

This results in only getting the values from 2022:

month	a2022
1	100
2	358
3	495

How could I change the SQL statement to get new columns for each year? Is this even possible?

Result should look like this (with a new column for each year, wouldn't mind if I had to update the SQL statement every year):

month	a2022	a2023
1	100	92
2	358	497
3	495	508

CodePudding user response：

You can use conditional aggregation:

select extract(month from bucket) bucket_month,
    sum("EnergyProduced") filter(where extract(year from bucket) = 2022) a_2022,
    sum("EnergyProduced") filter(where extract(year from bucket) = 2021) a_2021
from inverterlogs_summary_daily
where bucket >= date '2021-01-01' and bucket < date '2023-01-01'
group by extract(month from bucket)
order by bucket_month

I assumed that bucket is of a timestamp-like datatype, and adapted the date arithmetic accordingly.

Side note: the expressions in the filter clause can probably be optimized with the lengthier:

sum("EnergyProduced") filter(
    where bucket >= date '2022-01-01' and bucket < date '2023-01-01'
) a_2022,

CodePudding user response：

You can add a condition to the SUM

SELECT to_char(bucket, 'mm') as month,
sum(CASE WHEN extract (YEAR from inverterlogs_summary_daily.bucket) = 2022 then inverterlogs_summary_daily."EnergyProduced" END) as  a2022,
  sum(CASE WHEN extract (YEAR from inverterlogs_summary_daily.bucket) = 2023 then inverterlogs_summary_daily."EnergyProduced" END) as  a2022
from inverterlogs_summary_daily
WHERE
  inverterlogs_summary_daily.bucket >= '01.01.2022' and inverterlogs_summary_daily.bucket < '01.01.2024'
group by month