CODEWITHSUNDEEP
sql
racl
racl

Reputation: 3

SQL Query: Aggregate multiple values with Case and count it

I have the following query and I want the output to show only the count of Active Members and Non-Active Members:

  SELECT FORMAT(Date, 'dd-MM-yyyy') AS 'Date'
        ,CASE 
             WHEN Occupation = 'Active' THEN 'Active'
             WHEN Occupation IN ('Unemployed', 'Housewife/Student') THEN 'Non Active'   
         END AS Occupation
         ,COUNT (MemberID) as 'NumMember'
    
  FROM Member
  GROUP BY Date

The result I am getting is this:

17-02-2022  Non Active  1090
17-02-2022  Non Active  176
17-02-2022  Active      1011

Where the two lines "Non Active" are of this 2 status ('Unemployed', 'Housewife/Student'), I'would like to know how aggregate these 2 values in one to get only 'Active' and 'Non Active' status:

17-02-2022  Non Active  1266
17-02-2022  Active      1011

Thanks to everyone who tries to help

Upvotes: 0

Views: 366

Answers (1)

Marcio Rocha
Marcio Rocha

Reputation: 189

try to do with a totalizer of an subquery(your main query):

select 
    tmp.Date,
    tmp.Occupation,
    sum(tmp.NumMember) as NumMember
from (
    SELECT 
        FORMAT(Date, 'dd-MM-yyyy') AS 'Date',
        CASE 
            WHEN Occupation = 'Active' THEN 'Active'
            WHEN Occupation IN ('Unemployed', 'Housewife/Student') THEN 'Non Active'   
        END AS Occupation,
        COUNT (MemberID) as 'NumMember'
    FROM Member
) as tmp
group by tmp.Occupation,tmp.Date

Upvotes: 1

Related Questions