Skip to main content
Ben Nadel at RIA Unleashed (Nov. 2010) with: Marc Esher
Ben Nadel at RIA Unleashed (Nov. 2010) with: Marc Esher

Getting Group By Count In Left Outer Join

By on
Tags:

Sometimes I amazed that I just miss the most obvious solutions. I was running a query that had a left outer join on a list of vendors, joining to orders (purchase orders). I wanted to get the count of orders per vendor, so I was just getting the COUNT(*) of the Group BY.

The problem with this technique is that the COUNT(*) counts the vendor in the left outer join, not just the orders in the right portion of the join. To solve this, I started to do crazy queries involving sub-queries. It was nuts. Then, it dawned on me... I DON'T have to do COUNT(*). I can do counts on specific columns. And hence:

SELECT
	t.id,	
	t.name,	
	( ISNULL( COUNT(r.id), 0 ) ) AS reward_count,	
	( ISNULL( SUM(r.price), 0 ) ) AS price_sum,	
	( 0 ) AS reward_count_percent,	
	( 0 ) AS price_sum_percent
FROM
	vendor_type t
LEFT OUTER JOIN
	vendor_vendor_type_jn vtjn
ON
	t.id = vtjn.vendor_type_id
LEFT OUTER JOIN
	@reward r
ON
	vtjn.vendor_id = r.vendor_id
GROUP BY
	t.id,	
	t.name
  As you can see, the COUNT\(\) runs on r.id. This way, it can return a Type row with a count of zero. The only caveat is that you have to check for NULL values on the COUNT \(and the SUM\) since there might not be any records in the right portion of the JOIN.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/go/32

Reader Comments

1 Comments

Thanks this helped a bunch. I was trying to count the comments associated to a topic, and I didn't want to run subqueries. BTW, for MySQL the command is IfNull

1 Comments

Glad I came across your posting because it didn't dawn on me initially to do it this way either. Your post saved me from going down the crazy query road.

Thanks ...

15,674 Comments

@Mike,

Glad to help.

@Ruddy,

Thanks for the MySQL tip. I used to do only MS SQL, but now, we use mostly MySQL at work, so that helps.

1 Comments

rewrite the given query without using join and group by
select b.title,max(bc.returneddate-bc.checkoutdate) ''most days out'' from bookshelf_checkout bc,bookshelf b where bc.title(+)=b.title group by b.title;

1 Comments

Thanks for this blog. I was stuck with the COUNT(*) when I really wanted to count only one column. It didn't even click to me until I visit your bolg.

1 Comments

This SQL isn't syntatically correct as ISNULL() only takes 1 parameter. I would suggest something along the lines of:

IF( ISNULL( COUNT(r.id)), 0, COUNT(r.id) ) AS reward_count

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel