Postgresql group by and count() problem, missing row with 0 result 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?
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!