Sql server® 2012 t-sql fundamentals

Using the 

COUNT

 aggregate with Outer Joins

Another common logical bug involves using COUNT with outer joins. When you group the result of 

an outer join and use the COUNT(*) aggregate, the aggregate takes into consideration both inner 

rows and outer rows, because it counts rows regardless of their contents. Usually, you’re not supposed 

to take outer rows into consideration for the purposes of counting. For example, the following query 

is supposed to return the count of orders for each customer.

SELECT C.custid, COUNT(*) AS numorders 

FROM Sales.Customers AS C 

  LEFT OUTER JOIN Sales.Orders AS O 

    ON C.custid = O.custid 

GROUP BY C.custid;

However, the COUNT(*) aggregate counts rows regardless of their meaning or contents, and cus-

tomers who did not place orders—such as customers 22 and 57—each have an outer row in the result 

of the join. As you can see in the output of the query, shown here in abbreviated form, both 22 and 

57 show up with a count of 1, whereas the number of orders they placed is actually 0.

www.it-ebooks.info

---