0

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?

1
  • 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. – John K. N. Apr 15 '20 at 11:42
0

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!

2
  • Very nice solution, thank you very much! – STO Apr 15 '20 at 11:42
  • 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! :-) – Vérace Apr 15 '20 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.