Counting number of joined rows in left join
Solution 1
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.
Solution 2
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
Solution 3
Don't forget to use DISTINCT in case you will LEFT JOIN more than one table:
SELECT m.MESSAGEID, COUNT(DISTINCT mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;
errantlinguist
Updated on May 07, 2021Comments
-
errantlinguist about 3 years
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 compositePRIMARY 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 of0
for rows where the joined columns fromMESSAGEPART
areNULL
? 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?
-
Nick Krasnov over 10 yearsIt can be simplified a little.
sum(case....end)
can be replaced with simplecount(mp.messageid)
.Count(*)
counts, well, everything, including nulls andcount(col_name)
counts only non-null values. -
errantlinguist over 10 yearsThis is more complicated than the solution above, but I have to embed this query in another one anyway, so your solution was also ultimately helpful; cheers
-
Mark J. Bobak over 10 yearsThanks @NicholasKrasnov. I totally forgot that distinction. The older I get, the more I forget.... sigh.... :-)
-
DCShannon over 8 yearsThis was useful. I needed to see some other fields beside the one linking key. I found you can do that by adding the fields to both the select list and the group by clause.
-
godspeedelbow about 7 yearsAfter having tried tens of answers to other similar questions, this is one finally worked for me. It is the GROUP BY that I hadn't included earlier that makes this work, without having to use subqueries (you want to avoid that). Note: if you want to add a WHERE clause it'll need to look like this:
WHERE (mp.PARTNO = 1 OR mp.PARTNO IS NULL)
and is added before theGROUP BY
-
Andrew over 6 yearsbut what if you dont want to group by :(
-
petrosmm about 4 yearshowever inefficient this may or may not be but for purposes of knowledge, it is extremely useful especially right now!