Count how many active and inactive users are in a group/unit/department/team

Question

Given the following example data:

id	username	group	unit	department	team	status
1	user1	g1	u1	d1	t1	active
2	user2	g1	u1	d1	t2	active
3	user3	g1	u1	d1	t3	inactive
4	user4	g3	u6	d12	t30	active
5	user5	g25	u54	d70	t88	inactive

And the table definition for the table used above:

Create Table table_name(id INT, username VARCHAR(50), group_ VARCHAR(50), unit VARCHAR(50), department VARCHAR(50), team VARCHAR(50), status VARCHAR(50));

Insert Into table_name Values(1,'user1','g1','u1','d1','t1','active'),
(2,'user2','g1','u1','d1','t2','active'),
(3,'user3','g1','u1','d1','t3','inactive'),
(4,'user4','g3','u6','d12','t30','active'),
(5,'user5','g25','u54','d70','t88','inactive');

I get selections as arrays. Each array represents selection of group/unit/department/team, and I need to count how many active and inactive users are per the selection.

The selection does not necessarily have to include all the levels, so I can get the following selections for example:

["g1", "u1", "d1"] and ["g25", "u54", "d70", "t88"].

For this specific example, this is the query:

SELECT group_
     , unit
     , department
     , NULL as team
     , count(case when status='active' then 1 end) as active_count,
       count(case when status='inactive' then 1 end) as inactive_count
  FROM table_name
 WHERE group_='g1' 
   AND unit='u1' 
   AND department='d1'
group
    BY group_
     , unit
     , department
UNION ALL
SELECT group_
     , unit
     , department
     , team
     , count(case when status='active' then 1 end) as active_count,
       count(case when status='inactive' then 1 end) as inactive_count
  FROM table_name
 WHERE group_='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
group
    BY group_
     , unit
     , department
     , team

demo: https://dbfiddle.uk/tBEDulvw

But I might get dozens of selection arrays so I will have dozens of UNIONs and I think it is not efficient.

Is it possible to make a more efficient query for this purpose?

It needs to work for unknown number of selections but also where it's unknown which of the levels were selected (but you can assume that it has to be selected with the correct "hierarchy" structure, so if a department is in the array, you can assume that there has to be a unit and group as well)

Edit: Note that the number of arrays can change, for example I can get 3 arrays:

["g1", "u1", "d1"] and ["g25", "u54", "d70", "t88"] and ["g3", "u6"], then the query would be:

SELECT group_
     , unit
     , department
     , NULL as 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
 WHERE group_='g1' 
   AND unit='u1' 
   AND department='d1'
group
    BY group_
     , unit
     , department
UNION ALL
SELECT group_
     , unit
     , department
     , 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
 WHERE group_='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
group
    BY group_
     , unit
     , department
     , team
UNION ALL
SELECT group_
     , unit
     , NULL as department
     , NULL as 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
 WHERE group_='g3' 
   AND unit='u6' 
group
    BY group_
     , unit

demo: https://dbfiddle.uk/Nv2csSc8

Answer 1

As this answer to How do I select data with a case statement and group by? explains a subquery can be used to get the result. The subquery can conditionally select a value for the team using a CASE expression.

SELECT group_, unit, department, team, 
  count(case when status='active' then 1 end) as active_count,
  count(case when status='inactive' then 1 end) as inactive_count
FROM (
SELECT group_
     , unit
     , department
     , CASE WHEN group_='g1' AND unit='u1' AND department='d1' THEN NULL else team end as team
     , status
  FROM table_name
 WHERE (group_='g1' AND unit='u1' AND department='d1') OR 
  ( group_='g25'  AND unit='u54' AND department='d70' AND team='t88')
)  as groupedData
group BY group_, unit, department, team

DBFiddle sample

Below is a screenshot of the query plans for the original query as well as the query above. It illustrates how the query with the sub-query has one less table scan, three fewer scalar computations, one less stream aggregates and no concatenation (since it has no UNION).