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!
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! :-)
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.