Question 113898 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Use different fields based on condition

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
Tags:

2

Answers


  1. 0

    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,
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search