In my WHERE clause there are "groups" of selections separated by OR, for example:

WHERE (group_='g1' AND unit='u1' AND department='d1')
   OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')

or

WHERE (group_='g1' AND unit='u1' AND department='d1')
   OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
   OR (group_='g3' AND unit='u12')

Each of these groups may have different number of columns so some can be null. That's why I want to SELECT and GROUP BY the selected columns.

I tried this:

SELECT
    CASE
        WHEN team IS NULL
        THEN group_, unit, department
      
        WHEN team IS NULL AND department IS NULL
        THEN group_, unit

        WHEN team IS NULL AND department IS NULL AND unit IS NULL
        THEN group_

        ELSE
          group_, unit, department, team
    END

    COUNT(CASE WHEN status='active' then 1 END) AS active_count,
    COUNT(CASE WHEN status='inactive' then 1 END) AS inactive_count

FROM my_table
WHERE (group_='g1' AND unit='u1' AND department='d1')
   OR (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
   OR (group_='g3' AND unit='u6')
GROUP BY
    CASE
        WHEN team IS NULL
        THEN group_, unit, department
      
        WHEN team IS NULL AND department IS NULL
        THEN group_, unit

        WHEN team IS NULL AND department IS NULL AND unit IS NULL
        THEN group_

        ELSE
          group_, unit, department, team
    END
ORDER BY group_

But this is clearly wrong. Demo: https://dbfiddle.uk/X7PBwchn

What I'm trying to achieve is this:

I have the following data:

And I need to count active and inactive users based on each selection inside each group in the WHERE clause:

CodePudding user response：

If you fix your needed-NULL values to be NULL through CASE expressions and manage to do it correctly, you can apply aggregation directly.

SELECT [group], [unit],
       CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
            THEN [department] END AS [department],
       CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
             AND NOT ([group] = 'g1' AND [unit] = 'u1' AND [department] = 'd1')
            THEN [team] END AS [team],
       SUM(CASE WHEN [status] = 'active' 
                THEN 1 ELSE 0 END) AS [active_count],
       SUM(CASE WHEN [status] = 'inactive' 
                THEN 1 ELSE 0 END) AS [inactive_count]
FROM tab
WHERE ([group]='g1'  AND [unit]='u1'  AND [department]='d1')
   OR ([group]='g25' AND [unit]='u54' AND [department]='d70' AND [team]='t88')
   OR ([group]='g3'  AND [unit]='u6')
GROUP BY [group], [unit],
         CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
              THEN [department] END,
         CASE WHEN NOT ([group] = 'g3' AND [unit] = 'u6') 
               AND NOT ([group] = 'g1' AND [unit] = 'u1' AND [department] = 'd1')
              THEN [team] END

Check the demo here.

CodePudding user response：

To avoid copypasting groupings i'd create a cross apply with all your needed values:

SELECT
    
    gg.group_, gg.unit, gg.department, gg.team,
    COUNT(CASE WHEN status='active' then 1 END) AS active_count,
    COUNT(CASE WHEN status='inactive' then 1 END) AS inactive_count

FROM my_table m
cross apply (
    select 1 AS groupType, group_, unit, department, null as team
    where (group_='g1' AND unit='u1' AND department='d1')
    union all
    select 2, group_, unit, department, team
    where   (group_='g25' AND unit='u54' AND department='d70' AND team='t88')
    union all
    select 3, group_, unit, null, null
    where (group_='g3' AND unit='u6')
    ) gg
GROUP BY
    gg.groupType, gg.group_, gg.unit, gg.department, gg.team
ORDER BY gg.group_

This should probably simplify your code generation as well