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
Want to use code from this post? Check out the license.