COUNT Multiple Columns using GROUP BY – SQL

I am trying to create a GROUP BY COUNT of ~30 columns in my database. The database is basically a shiftplan, where on each column a row can be assigned different shift types (denoted as a D, N, A, X, F, C, etc..).

I can use the following query to get a count of each shift type, am struggling to work out the best way to copy this across 30 more columns:

SELECT day1, COUNT(*) FROM shift_plan
GROUP BY day1;

This gives me the output:

day1,count
---------
D,1840
N,234
X,442
F,19
C,116

Which is close to what I want, I just want to carry it across to all other 30 workdays and end up with something more like this:

Shift,day1,day2,day3
----------------
D,1840,1594,1622
N,234,234,233
X,442,552,553
F,19,20,24
C,116,134,144

Thanks a lot for any advice you can give!

Answer

I think that you want a cross join with a fixed list of values, then conditional aggregation:

select
    s.shift,
    count(*) filter(where p.day1 = s.shift) day1,
    count(*) filter(where p.day2 = s.shift) day2,
    count(*) filter(where p.day3 = s.shift) day3,
    ...
from (values ('D'), ('N'), ('X'), ('F'), ('C')) s(shift)
cross join shift_plan p
group by s.shift