Using multiple count with multiple where clauses

Question

I am trying to write a SQL query where I want to count the number of existence of Q1, Q2, Q3 and Q4 in Quarter. I have tried looking the previous questions but they weren't much clear. Here's my code:

SELECT  
COUNT(Quarter CASE WHEN Quarter = 'Q1') 
COUNT(Quarter CASE WHEN Quarter = 'Q2') 
COUNT(Quarter CASE WHEN Quarter = 'Q3') 
COUNT(Quarter CASE WHEN Quarter = 'Q4') 
FROM office.office;

What am I doing wrong?

Tim Biegeleisen · Accepted Answer · 2020-12-04 05:27:54Z

2

You want conditional aggregation here. Here is a terse way of doing this on MySQL by summing boolean expressions:

SELECT  
    SUM(Quarter = 'Q1') q1_total,
    SUM(Quarter = 'Q2') q2_total,
    SUM(Quarter = 'Q3') q3_total,
    SUM(Quarter = 'Q4') q4_total
FROM office.office;

If you wanted to use COUNT, here is the correct syntax for that:

COUNT(CASE WHEN Quarter = 'Q1' THEN 1 END) q1_total  -- and so on

answered Dec 4, 2020 at 5:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using multiple count with multiple where clauses

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related