Question 90223 in PostgreSQL Question posted in
The official documentation can be found here.

SQL multiple data count from multiple tables with union all – Postgresql

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,

enter image description here

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,

enter image description here

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.

Tags:

2

Answers


  1. 0

    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.
    We could create a table or a view.

    CREATE VIEW 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      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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search