CODEWITHSUNDEEP
sql
Roho
Roho

Reputation: 101

CASE STATEMENT FOR GETTING COUNT

I'm trying to count the number of accounts completed vs accounts start for each month in a year.

Column eventlabel from my table provides the information of start and completed, so want to use that and count how many start and how many completed in that month.

I want to count how many eventlabel are "start" for that particular month, and how many eventlabel are "completed" for that month, and give the count value in a new column renamed as below.

I want the result in the below manner:

month year acc_start acc_completed
----------------------------------
09    2021  10         9
11    2021  40         30
12    2021  20         15

This is the query I used, but it's unsuccessful:

SELECT
    month, year,
    CASE 
        WHEN eventLabel = 'new registration - completed' 
            THEN COUNT(*) AS acc_completed
        WHEN eventLabel = 'new registration - start' 
            THEN COUNT(*) AS acc_start 
    END 
FROM
    tbl_hits
GROUP BY  
    snapshot_month, snapshot_year

Pls help.

Upvotes: 0

Views: 1802

Answers (1)

tinazmu
tinazmu

Reputation: 5139

select month,year,
   count(CASE WHEN eventLabel='new registration - completed' 
              THEN 1 END) as acc_completed,
   count(WHEN eventLabel='new registration - start'
              THEN 1 END) as acc_start 
from tbl_hits
group by snapshot_month,snapshot_year

Everything outside the aggregate functions (COUNT, in this case) must be in the GROUP BY, so we move the check inside the COUNT. If the condition is not satisfied the result of CASE WHEN is NULL and the COUNT will ignore.

Upvotes: 1

Related Questions