Postgresql group by and count() problem, missing row with 0 result

Question

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?

• John K. N.: You wrote that you had tried all variations of JOIN. Could you edit your question and add what you have tried so far? It might be just a minor issue. Welcome to DBA.SE.

Answer 1

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!

• Vérace: My pleasure - don't hesitate to come back to us with any other database problems. Only next time, could you please provide your tables as DDL (CREATE TABLE foo (...);) and your data as DML (INSERT INTO foo VALUES (...);)? Or you could even provide a fiddle (with the stuff here also). Help us to help you! :-)