Counting number of joined rows in left join
I'm trying to write an aggregate query in SQL which returns the count of all records joined to a given record in a table; If no records were joined to the given record, then the result for that record should be 0
:
Data
My database looks like this (I'm not able to change the structure, unfortunately):
MESSAGE
----------------------------------------------
MESSAGEID SENDER SUBJECT
----------------------------------------------
1 Tim Rabbit of Caerbannog
2 Bridgekeeper Bridge of Death
MESSAGEPART
----------------------------------------------
MESSAGEID PARTNO CONTENT
----------------------------------------------
1 0 (BLOB)
1 1 (BLOB)
3 0 (BLOB)
(MESSAGEPART
has a composite PRIMARY KEY("MESSAGEID", "PARTNO")
)
Desired output
Given the data above I should get something like this:
MESSAGEID COUNT(*)
-----------------------------------------------
1 2
2 0
It seems obvious that I need to do a left join on the MESSAGE
table, but how do I return a count of 0
for rows where the joined columns from MESSAGEPART
are NULL
? I've tried the following:
Logic
I've tried
SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
However, this returns
MESSAGEID COUNT(*)
-----------------------------------------------
1 2
2 1
I've also tried
SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY mp.MESSAGEID;
but this returns
MESSAGEID COUNT(*)
-----------------------------------------------
1 2
1
What am I doing wrong here?
How about something like this:
SELECT m.MESSAGEID, sum((case when mp.messageid is not null then 1 else 0 end)) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
The COUNT() function will count every row, even if it has null. Using SUM() and CASE, you can count only non-null values.
EDIT: A simpler version taken from the top comment:
SELECT m.MESSAGEID, COUNT(mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
Hope that helps.
You first want to count in your messaepart table before joining, i think. Try this:
SELECT m.MessageId
, COALESCE(c, 0) as myCount
FROM MESSAGE m
LEFT JOIN (SELECT MESSAGEID
, count(*) c
FROM MESSAGEPART
GROUP BY MESSAGEID) mp
ON mp.MESSAGEID = m.MESSAGEID