COUNT Multiple Columns using GROUP BY - SQL

You will have to copy the unions a bunch of times, but this would work.

    SELECT
     shift,
     MAX(day1) day1,
     MAX(day2) day2,
     MAX(day3) day3
 FROM
     (
         SELECT
             day1   shift,
             COUNT(*) day1,
             NULL day2,
             NULL day3
         FROM
             shift_plan
         GROUP BY
             day1
         UNION
         SELECT
             day2   shift,
             NULL day1,
             COUNT(*) day2,
             NULL day3
         FROM
             shift_plan
         GROUP BY
             day2
         UNION
         SELECT
             day3   shift,
             NULL day1,
             NULL day2,
             COUNT(*) day3
         FROM
             shift_plan
         GROUP BY
             day3
     ) z
 GROUP BY
     shift

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

If you use GROUP BY, you have to specify all the column names in the group by that are used in select and are not part of an aggregate function like SUM, COUNT, MIN, MAX, etc.