Counting Number of Joined Rows in Left Join

Counting number of joined rows in 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 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;

Count records only from left side of a LEFT JOIN

just take the aggregate sum first on t2, then join with t2 like this:

SELECT t1.TripID, COUNT(t1.SampleID) AS Samples, SUM(t3.Bugs) as Bugs
FROM tbl_Sample AS t1
LEFT Join (
  SELECT t2.SampleID, SUM(t2.C1 + t2.C2) as Bugs
  FROM tbl_Bugs as t2
  GROUP BY SampleID) AS t3 ON t1.SampleID = t3.SampleID
GROUP BY t1.TripID

How do I get a count of associated rows in a left join in MySQL?

SELECT 
    `vehicle`.`id`, 
    `vehicle`.`stock`, 
    `vehicle`.`year`, 
    `vehicle`.`make`, 
    `vehicle`.`model`, 
    `images`.`name`,
    (
        SELECT COUNT(*) 
        FROM `images` 
        WHERE `vehicle_id` = `vehicle`.`id`
    ) AS `image_count`
FROM `vehicle`
LEFT JOIN `images`
ON `images`.`vehicle_id` = `vehicle`.`id`
WHERE `images`.`default`

MySQL - Counting rows and left join problem

I'd opt for something like:

SELECT 
    c.id AS campaign_id, 
    COUNT(cc.id) AS code_count
FROM 
    campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
AND cc.status = 0 -- Having this clause in the WHERE, effectively makes this an INNER JOIN
WHERE c.partner_id = 4
GROUP BY c.id

Moving the AND to the join clause makes the join succeed or fail, crucially keeping resulting rows in where there is no matching row in the 'right' table.

If it were in the WHERE, the comparisons to NULL (where there is no campaign_code) would fail, and be eliminated from the results.

Left joining count(*) from second table

I would suggest using a correlated subquery:

SELECT p.post_title, p.post_author, p.post_date, p.post_article,
       (SELECT COUNT(*)
        FROM comments c
        WHERE c.comment_post_id = p.post_id
       ) as num_comments
FROM posts p
WHERE p.post_id = 25;

This efficiently allows you to have the 25 only once in the query -- reducing the scope for coding errors.

Your code doesn't work because you are not selecting the count column in the outer SELECT. Remember: A SQL query describes the result set. If you don't have a column or expression in the SELECT, it is not going to be selected.

How to count records after joining multiple tables in SQL

An alternative approach is to use APPLY in the FROM to get the counts:

USE mydb;

SELECT emp.ecode,
       Sc.schedulecount,
       O.noordercount,
       St.salescount
FROM dbo.employee emp
     CROSS APPLY (SELECT COUNT(*) AS schedulecount
                  FROM dbo.schedule sch
                  WHERE sch.user_id = emp.ecode) Sc
     CROSS APPLY (SELECT COUNT(*) AS noordercount
                  FROM dbo.[order] ord --Generally it's a good idea to avoid Reserved Keywords for Object names
                  WHERE ord.ecode = emp.ecode) O
     CROSS APPLY (SELECT COUNT(*) AS salescount
                  FROM dbo.store sto
                  WHERE sto.ecode = emp.ecode) St
ORDER BY emp.ecode DESC;

db<>fiddle showing results are correct per question.

Crystal ball:

SELECT emp.ecode,
       Sc.schedulecount,
       O.noordercount,
       St.salescount
FROM dbo.employee emp
     CROSS APPLY (SELECT COUNT(*) AS schedulecount
                  FROM dbo.schedule sch
                  WHERE sch.user_id = emp.ecode) Sc
     CROSS APPLY (SELECT COUNT(*) AS noordercount
                  FROM dbo.[order] ord --Generally it's a good idea to avoid Reserved Keywords for Object names
                  WHERE ord.ecode = emp.ecode) O
     CROSS APPLY (SELECT COUNT(*) AS salescount
                  FROM dbo.store sto
                  WHERE sto.ecode = emp.ecode) St
WHERE Sc.schedulecount > 0  
   OR O.noordercount > 0
   OR St.salescount > 0
ORDER BY emp.ecode DESC;

---

Related Topics