CASE STATEMENT FOR GETTING COUNT

January 13, 2022

Im trying to count the number of accounts completed vs accounts start for each month in an 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

I used the below query but 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.

>Solution :

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.