Ben Nadel © 2008.
All content is the property of Ben Nadel and Kinky Solutions.
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:
Launch code in new window » Download code as text file »
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.
Download Code Snippet ZIP File
Comments (5) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
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.
Posted by Gareth Arch on Jun 6, 2007 at 10:56 AM
@Gareth,
You might be correct on that, cause COUNT() should return zero if there are no records. Good call.
Posted by Ben Nadel on Jun 6, 2007 at 11:52 AM
Thanks for this... I was also about to embark on a crazy sub-query journey.
Posted by Nick Pearce on Feb 11, 2008 at 1:41 AM
thanks for the code.
Posted by Gsm Lobby on Mar 21, 2008 at 12:26 PM
Thank You!
Posted by jimmy on Apr 11, 2008 at 6:09 PM