SQL Query: Aggregate multiple values with Case and count it-CodePudding

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

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

CodePudding user response：

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

CodePudding user response：

What you need is Conditional aggregation along with GROUP BY clause for non-aggregated column(s) such as

SELECT FORMAT(Date, 'dd-MM-yyyy') AS Date,
       MIN(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 FORMAT(Date, 'dd-MM-yyyy')