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

Postgresql – Sql Count(*) from multiple tables but intersection should come

Select distinct(job_id) from A where condition
Select distinct(job_id) from B where condition
Select distinct(job_id) from C where condition

Here may be 1,2 and 3 have some common jobId
What I need is count of all job_id which may not get repeated.

Tags:

2

Answers


  1. 0

    With UNION ?

    Select job_id from A where condition
UNION
Select job_id from B where condition
UNION
Select job_id from C where condition
    Login or Signup to reply.
  2. 0

    I believe EXCEPT is what you might be looking for here which provides a disjoint result set across multiple queries. This could probably be handled with JOIN logic also but it’s a little hard to tell with the limited detail in the question.

    Here is the Postgres documentation on the EXCEPT keyword.

    WITH data AS
(
    SELECT job_id FROM A WHERE condition
    EXCEPT
    SELECT job_id FROM B WHERE condition
    EXCEPT
    SELECT job_id FROM C WHERE condition
)
SELECT COUNT(*) FROM data;

    Here is a solution that works but is not scalable. I’m hesitant to post it because I feel like it’s ugly and there must be a better way but maybe this will point you in the right direction.

    WITH a_b_dups AS
(
  SELECT job_id FROM a
  INTERSECT
  SELECT job_id FROM b
), b_c_dups AS
(
  SELECT job_id FROM b
  INTERSECT
  SELECT job_id FROM c
), a_c_dups AS
(
  SELECT job_id FROM a
  INTERSECT
  SELECT job_id FROM c
), dups AS
(
  SELECT job_id FROM a_b_dups
   UNION
  SELECT job_id FROM b_c_dups
   UNION
  SELECT job_id FROM a_c_dups
), uniques AS
(
  SELECT job_id FROM a
   UNION
  SELECT job_id FROM b
   UNION
  SELECT job_id FROM c
  EXCEPT
  SELECT job_id FROM dups
)
SELECT COUNT(*) FROM uniques;

    DBFiddle to show a working example.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search