SQL multiple data count from multiple tables with union all-CodePudding

My existing SQL looks like the below, it will generate only the total_pending_req count.

SELECT count(table1.employee_code) as total_requests, table1.employee_code as emp_code
FROM table1
WHERE employee_status = 'PENDING'
GROUP BY emp_code

UNION ALL

SELECT count(table2.employee_code) as total_requests, table2.employee_code as emp_code
FROM table2
WHERE employee_status = 'PENDING'
GROUP BY emp_code

UNION ALL

SELECT count(table3.employee_code) as total_requests, table3.employee_code as emp_code
FROM table3
WHERE employee_status = 'PENDING'
GROUP BY emp_code

This will return the result below,

I want to get the request count as total_pending_req, total_rejected_req and total_completed_req considering 3 different tables. All tables have the same status codes, PENDING, COMPLETED and REJECTED. The final result should be like this,

I would like to have an idea, of how to extract data more efficiently, since I have to use UNION ALL. May I know if there is any better approach to extract data more efficiently? I would appreciate your help on this.

CodePudding user response：

The fact that your rows appear in the several table{1,2,3} relations is just an annoying distraction. Let's make it a single relation, already.

CREATE TABLE table_combined AS
(SELECT * FROM table1
 UNION ALL
 SELECT * FROM table2
 UNION ALL 
 SELECT * FROM table3
 UNION ALL
)

Good!

With that out of the way, it becomes a trivial GROUP BY.

SELECT    employee_code, employee_status, COUNT(*)
FROM      table_combined
GROUP BY  employee_code, employee_status

You can phrase it as a CTE if you're averse to DDL. Or create a VIEW that does the UNION ALL heavy lifting. Or a MATERIALIZED VIEW, whatever.

The output format is three emps × three statuses, or nine rows.

If you really need three rows, feel free to SELECT from that relation to re-format it.

CodePudding user response：

you can use CASE and Union All as follows

select 
table1.employee_code as emp_code,
case employee_status = 'PENDING' then count(table1.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table1.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table1.employee_code)  else 0 end as REJECTED   
from FROM table1
GROUP BY emp_code
UNION ALL
select 
table2.employee_code as emp_code,
case employee_status = 'PENDING' then count(table2.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table2.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table2.employee_code)  else 0 end as REJECTED   
from FROM table2
GROUP BY emp_code
UNION ALL
select 
table3.employee_code as emp_code,
case employee_status = 'PENDING' then count(table3.employee_code)  else 0 end as PENDING,
case employee_status = 'COMPLETED' then count(table3.employee_code)  else 0 end as COMPLETED,
case employee_status = 'REJECTED' then count(table3.employee_code)  else 0 end as REJECTED   
from FROM table3
GROUP BY emp_code

Or using SUM and Case

SELECT 
table1.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table1
GROUP BY emp_code
UNION ALL
SELECT 
table2.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table2
GROUP BY emp_code
UNION ALL
SELECT 
table3.employee_code as emp_code,
SUM(CASE employee_status = 'PENDING' Then 1 Else 0 End ) as PENDING,
SUM(CASE employee_status = 'COMPLETED' Then 1 Else 0 End ) as COMPLETED,
SUM(CASE employee_status = 'REJECTED' Then 1 Else 0 End ) as REJECTED
from FROM table3
GROUP BY emp_code