A filtered aggregate calculates an aggregate over only rows matching a condition. Multiple filtered aggregates can turn grouped power data into a fixed-column report, such as yearly production per system. section 4.7
Yearly power production by system
SELECT system_id, sum(kWh) FILTER (WHERE year(day) = 2019) AS 'kWh in 2019', sum(kWh) FILTER (WHERE year(day) = 2020) AS 'kWh in 2020' FROM v_power_per_day GROUP BY system_id; The result keeps systems as rows and produces separate 2019 and 2020 production columns. section 4.7
Listing 4.25 Statically pivoting a result by applying a filter to all aggregates selected
SELECT system_id,
sum(kWh) FILTER (WHERE year(day) = 2019)
AS 'kWh in 2019',
sum(kWh) FILTER (WHERE year(day) = 2020)
AS 'kWh in 2020'
FROM v_power_per_day
GROUP BY system_id;
Inspect the complete SQL listing for a static pivot using filtered sum aggregates. section 4.7
