PostgreSQL Group By and Count() Issue – Missing Row with 0 Result

join;postgresql

I have a problem with group by and count().
I need a result table looks like below.
Tried I guess all the variations of JOIN.

There are two types of wrong result:
– first when the row "3 C 0" is missing,
– the second when it is present but with a false value "3 C 1"

Table1: 

id name  
1  A  
2  B  
3  C  
4  D

Table2: 

id  
1  
1  
2  
1  
4

result should look like this: 

id name count  
1  A    3  
2  B    1  
3  C    0  
4  D    1

Any ideas?

Best Answer

Basically, you want a subquery like this:

SELECT t1.id1, t1.let, COALESCE(tab.cnt, 0) AS my_cnt
FROM t1
LEFT JOIN
(
  SELECT 
    id2, 
    COUNT(id2) AS cnt
  FROM t2
  GROUP BY id2
  -- ORDER BY id2
) tab
ON t1.id1 = tab.id2;

See the fiddle here.

Basically, what I did was:

Create tables t1 and t2:

CREATE TABLE t1
(
  id1 int,
  let text
);

and

CREATE TABLE t2 
(
  id2 int
);

Populate them:

INSERT INTO t1 
VALUES 
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D');

and

INSERT INTO t2 
VALUES 
(1), (1), (2), (1), (4);

Ran my query:

SELECT t1.id1, t1.let, COALESCE(tab.cnt, 0) AS my_cnt
FROM t1
LEFT JOIN
(
  SELECT 
    id2, 
    COUNT(id2) AS cnt
  FROM t2
  GROUP BY id2
  -- ORDER BY id2
) tab
ON t1.id1 = tab.id2;

Result:

id1 let my_cnt
1   A   3
2   B   1
3   C   0
4   D   1

The inner query gets the counts and then the LEFT JOIN causes the id1 of 3 to be present - otherwise it would be missing from the result set and the COALESCE function (see here also) causes the value 0 to be present instead of NULL which would otherwise be there. I initially misread the question - p.s. welcome to the forum!

Related Question