Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with:

Getting Group By Count In Left Outer Join

By Ben Nadel on
Tags: SQL

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.

Tweet This Deep thoughts by @BenNadel - Getting Group By Count In Left Outer Join Thanks my man — you rock the party that rocks the body!



Reader Comments

Wouldn't you only have to do the ISNULL for SUM? COUNT (at least on MSSQL) should have a count of 0 (zero) for NULL values.

Reply to this Comment

@Gareth,

You might be correct on that, cause COUNT() should return zero if there are no records. Good call.

Reply to this Comment

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

Reply to this Comment

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 ...

Reply to this Comment

@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.

Reply to this Comment

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;

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

@George,

What SQL engine are you using? I definitely ran this code before it was posted and it worked just fine.

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.