My input table has count of values for events belonging to specific categories for every year.

I need to show a sum of the events for each category for each year, in the specific case I'd also need to aggregate D1 D2 D3 together).

Expected Output:

The main problem of my attempts comes from the fact that there are challenges from: 1 - years that dont have events in certain categories, 2 - the years are only 2 for now, but will grow as time goes by

I tried breaking the sql with several views but cannot preview in future years the cases where some categories don't have values.

I'd like to have a sql query that handles future cases.

Thanks for any help.

CodePudding user response：

This is the case of a pivoting task.

In your specific case the CASE expressions help you to gather specific count of events to be summed up, then you can aggregate on your year_ values. You can detect the D values with the LIKE operator.

SELECT year_, 
       SUM(events_)                                              AS events_total,
       SUM(CASE WHEN category    = 'A'  THEN events_ ELSE 0 END) AS categoryA,
       SUM(CASE WHEN category    = 'B'  THEN events_ ELSE 0 END) AS categoryB,
       SUM(CASE WHEN category    = 'C'  THEN events_ ELSE 0 END) AS categoryC,
       SUM(CASE WHEN category LIKE 'D%' THEN events_ ELSE 0 END) AS categoryD
FROM tab
GROUP BY year_

Check the demo here.

Using the FILTER operator, combined with the COALESCE function, would come out like this:

SELECT year_, 
       SUM(events_)                                               AS events_total,
       COALESCE(SUM(events_) FILTER(WHERE category    = 'A' ), 0) AS catA,
       COALESCE(SUM(events_) FILTER(WHERE category    = 'B' ), 0) AS catB,
       COALESCE(SUM(events_) FILTER(WHERE category    = 'C' ), 0) AS catC,
       COALESCE(SUM(events_) FILTER(WHERE category LIKE 'D%'), 0) AS catD
FROM tab
GROUP BY year_

Check the demo here.