Counting number of joined rows in left join

Question

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?

Answer 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.