Counting number of joined rows in left join

count, join, left-join, oracle, sql

Solution

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;

Problem

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?

Original source