AnswerBun.com

COUNT Multiple Columns using GROUP BY - SQL

Stack Overflow Asked by Barnee on January 1, 2022

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!

3 Answers

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

Answered by Mike Lum on January 1, 2022

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

Answered by GMB on January 1, 2022

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.

Answered by Thirumal on January 1, 2022

Add your own answers!

Related Questions

Get data from DataGrid if DataGridCheckBoxColumn is checked

0  Asked on November 4, 2021 by user12711263

     

The value of local variable isn’t used

1  Asked on November 4, 2021 by greenycodeskii

     

clickhouse : information_schema.KEY_COLUMN_USAGE

1  Asked on November 4, 2021 by sravya-jasthi

 

WPF prevent combobox selection change under condition

1  Asked on March 8, 2021 by hasan-h

     

How to listen to Boot Complete broadcast in Oneplus?

1  Asked on March 8, 2021 by saarang-tiwari

 

Indirection requires pointer operand error

1  Asked on March 8, 2021 by mr-needhelp

 

Point Cloud Segmentation

1  Asked on March 7, 2021 by aldi-lin

   

Angular HTTP get request is not working in ngOnInit

1  Asked on March 6, 2021 by iamgrooot

   

CheckedChanged not triggering

0  Asked on March 6, 2021

     

flutter splash screen is blurry on physical device

1  Asked on March 5, 2021 by mohamed-mostapha

   

Nginx syslog post request

1  Asked on March 5, 2021 by andreas-hunter

         

Cloning Objects in Google Optimize

0  Asked on March 4, 2021 by jpfotoz

     

Specialize how std::vector grows

3  Asked on March 3, 2021 by gary-allen

   

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir