78

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?

errantlinguist
  • 3,658
  • 4
  • 18
  • 41
  • For a start, use `m.MESSAGEID` (will always be there) rather than `mp.MESSAGEID` (will only be there when there's a match). – Anthony Grist Nov 21 '13 at 11:39

4 Answers4

108

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;
starball
  • 20,030
  • 7
  • 43
  • 238
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • 19
    It can be simplified a little. `sum(case....end)` can be replaced with simple `count(mp.messageid)`. `Count(*)` counts, well, everything, including nulls and `count(col_name)` counts only non-null values. – Nick Krasnov Nov 21 '13 at 12:06
  • 1
    Thanks @NicholasKrasnov. I totally forgot that distinction. The older I get, the more I forget.... sigh.... :-) – Mark J. Bobak Nov 21 '13 at 21:44
  • This 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. – DCShannon Oct 22 '15 at 21:06
  • After 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 the `GROUP BY` – godspeedelbow Apr 22 '17 at 21:22
  • but what if you dont want to group by :( – Andrew Oct 25 '17 at 18:54
22

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
oerkelens
  • 5,053
  • 1
  • 22
  • 29
  • This 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 – errantlinguist Nov 21 '13 at 12:32
  • however inefficient this may or may not be but for purposes of knowledge, it is extremely useful especially right now! – petrosmm Apr 07 '20 at 23:51
7

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;
Mike
  • 91
  • 1
  • 2
1

Return one number as a total of the matching elements between two tables, based on matching columns

In my case, I needed one total number returned for the number/count of matching items from a particular column and from two different tables.

For example, I have two separate tables that each have a PhoneNumber column. Between those two tables, I want to know how many from that column match.

Reference: https://www.guru99.com/joins.html

Using the same tables name above, it would look like this:

SELECT COUNT(DISTINCT m.MESSAGEID) AS COUNT FROM MESSAGE m, MESSAGEPART mp
where mp.MESSAGEID = m.MESSAGEID;
Amiri
  • 41
  • 3