Counting number of joined rows in left join

sql oracle join count 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?

For a start, use m.MESSAGEID (will always be there) rather than mp.MESSAGEID (will only be there when there's a match).

pjam

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.

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.

Thanks @NicholasKrasnov. I totally forgot that distinction. The older I get, the more I forget.... sigh.... :-)

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.

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

but what if you dont want to group by :(

oerkelens

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

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

however inefficient this may or may not be but for purposes of knowledge, it is extremely useful especially right now!

Mike

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;

Amiri

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;

Counting number of joined rows in left join

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US