COUNT Multiple Columns using GROUP BY - SQL

Question

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!

Mike Lum · Answer

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

GMB · 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

Thirumal · Answer

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.

COUNT Multiple Columns using GROUP BY - SQL

3 Answers

Add your own answers!

Ask a Question