Counting number of joined rows in left join Counting number of joined rows in left join oracle oracle

Counting number of joined rows in left join


sql oracle join count left-join

How about something like this:

SELECT m.MESSAGEID, sum((case when mp.messageid is not null then 1 else 0 end)) FROM MESSAGE mLEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEIDGROUP 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 mLEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEIDGROUP BY m.MESSAGEID;

Hope that helps.

sql oracle join count left-join

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 mLEFT JOIN (SELECT MESSAGEID                , count(*) c              FROM MESSAGEPART             GROUP BY MESSAGEID) mp       ON mp.MESSAGEID = m.MESSAGEID

sql oracle join count left-join

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 mLEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEIDGROUP BY m.MESSAGEID;

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last