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

Postgresql – select count from both tables using join in postgesql

how to find number of records in both table using join.
i have two tables table1 and table2 with same structure.

table1

id item
1 A
1 B
1 C
2 A
2 B

table2

id item
1 A
1 B
2 A
2 B
2 C
2 D

Output should be like this.

id table1.itemcount table2.itemcount
1 3 2
2 2 4
Tags:

2

Answers


  1. 0 
    SELECT DISTINCT id, (
  SELECT COUNT(*) FROM table1 AS table1_2 WHERE table1_2.id=table1.id
) AS "table1.itemcount", (
  SELECT COUNT(*) FROM table2 AS table2_2 WHERE table2_2.id=table1.id
) AS "table2.itemcount"
FROM table1;
    Login or Signup to reply.
  2. 0

    Assuming that each id is guaranteed to exist in both tables, the following would work

    select 
    t1.id, 
    count(distinct t1.item) t1count, 
    count(distinct t2.item) t2count 
from t1 
join t2 on t1.id = t2.id
group by 1;

    But if that is not guaranteed then we’ll have to use full outer join to get unique ids from both tables

    select 
    coalesce(t1.id, t2.id) id, 
    count(distinct t1.item) t1count, 
    count(distinct t2.item) t2count 
from t1 
full outer join t2 on t1.id = t2.id
group by 1;

    We’re using coalesce here as well for id because if it only exists in t2, t1.id would result in null.

    @DeeStark’s answer also works if ids are guaranteed to be in both tables but it’s quite inefficient because count is essentially run twice for every distinct id in the table. Here’s the fiddle where you can test out different approaches. I’ve prefixed each query with explain which shows the cost

    Hope this helps

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

Back To Top
Search