Sql server® 2012 t-sql fundamentals

 

CHAPTER 3

 

Joins

 

 119

custid      numorders 

----------- ----------- 

1           6 

2           4 

3           7 

4           13 

5           18 

... 

22          1 

... 

57          1 

... 

87          15 

88          9 

89          14 

90          7 

91          7 

 

(91 row(s) affected)

The COUNT(*) aggregate function cannot detect whether a row really represents an order. To fix 

the problem, you should use COUNT() instead of COUNT(*), and provide a column from 

the nonpreserved side of the join. This way, the COUNT() aggregate ignores outer rows because they 

have a NULL in that column. Remember to use a column that can only be NULL, in case the row is an 

outer row—for example, the primary key column orderid.

SELECT C.custid, COUNT(O.orderid) AS numorders 

FROM Sales.Customers AS C 

  LEFT OUTER JOIN Sales.Orders AS O 

    ON C.custid = O.custid 

GROUP BY C.custid;

Notice in the output shown here in abbreviated form that the customers 22 and 57 now show up 

with a count of 0.

custid      numorders 

----------- ----------- 

1           6 

2           4 

3           7 

4           13 

5           18 

... 

22          0 

... 

57          0 

... 

87          15 

88          9 

89          14 

90          7 

91          7 

 

(91 row(s) affected)

www.it-ebooks.info

---