How to combine each group in the union into single row?-CodePudding

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 following query which groups results by active and inactive per each selected group/unit/department/team, for example:

SELECT group
     , unit
     , department
     , NULL as team
     , COUNT(*) AS count
  FROM table
 WHERE group='g1' 
   AND unit='u1' 
   AND department='d1'
GROUP
    BY group
     , unit
     , department
UNION ALL
SELECT group
     , unit
     , department
     , team
     , COUNT(*) AS count
  FROM table
 WHERE group='g25' 
   AND unit='u54' 
   AND department='d70' 
   AND team='t88'
GROUP
    BY group
     , unit
     , department
     , team

this results in the following:

group	unit	department	team	status	count
g1	u1	d1	NULL	active	2
g1	u1	d1	NULL	inactive	1
g25	u54	d70	t88	active	0
g25	u54	d70	t88	inactive	1

Is it possible to turn each of the groups to a single row, i.e.:

group	unit	department	team	active_count	inactive_count
g1	u1	d1	NULL	2	1
g25	u54	d70	t88	1	0

CodePudding user response：

You may use the conditional count function instead of count as the following:

count(case when status='active' then 1 end) as active_count,
count(case when status='inactive' then 1 end) as inactive_count

According to the provided logic, I think you could rebuild your query without a union as the following:

select group_, unit, department, 
       case when group_='g1' then null else team end 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') or
      (group_='g25' and unit='u54' and department='d70' and team='t88')
group by group_, unit , department,
         case when group_='g1' then null else team end
order by group_

See demo

CodePudding user response：

I preserve your solution and made some updates on it using the conditional count on status :

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
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
WHERE _group='g25' AND unit='u54' AND department='d70' AND team='t88'
GROUP BY _group, unit, department, team

Demo here : https://dbfiddle.uk/OBD2Xob3