Displaying rows in SQL Server where COUNT = 0

Question

The following query only returns Region Names for regions where there have been orders.

   SELECT r.RegionName,
          COUNT (DISTINCT o.uid)
     FROM Orders AS o
LEFT JOIN Customers AS c ON o.CustomerID = c.uid
LEFT JOIN Regions AS r ON c.Region = r.uid
    WHERE (r.RegionName NOT LIKE 'NULL') 
      AND (r.RegionName <> '') 
      AND (r.RegionName NOT LIKE 'Region 1') 
      AND (o.DateOrdered LIKE '7%2011%')
 GROUP BY r.RegionName
 ORDER BY r.RegionName

How can I modify it so that all region names show up even when the "COUNT" is "0"?

Tom H · Accepted Answer · 2011-07-07 19:51:44Z

6

You need to either change your JOIN to Regions to be a RIGHT JOIN or make Regions the FROM table and then JOIN to the other tables from there.

I prefer the second method, since it seems more intuitive to me. You care about Regions here and you're trying to get information about Regions, so that should be in the FROM (IMO):

SELECT
    R.RegionName,
    COUNT(O.uid)
FROM
    Regions R
LEFT OUTER JOIN Customers C ON C.Region = R.uid  -- I really don't like this naming convention
LEFT OUTER JOIN Orders O ON
    O.CustomerID = C.uid AND
    O.DateOrdered LIKE '7%2011%'  -- Is your date really stored as a string? Ugh!
WHERE
    R.RegionName <> 'NULL' AND   -- This is VERY bad...
    R.RegionName <> '' AND
    R.RegionName <> 'Region 1'
GROUP BY
    R.RegionName
ORDER BY
    R.RegionName

edited Jul 7, 2011 at 19:51

answered Jul 7, 2011 at 19:25

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

niktrs Over a year ago

+1. Comments: I think R.RegionName <> 'NULL' AND R.RegionName <> '' can be ommited since Region is the main table. Also DISTINCT O.uid can be ommited if there is only one row for each order.

Tom H Over a year ago

You're right, if uid is the PK for Orders then that can be omitted. I figured that the "NULL" and "''" were to account for bad data in the Regions table, but maybe I'm wrong there.

Scott Over a year ago

Thank you for your assistance. Naming and field types and missing data out of my control unfortunatly.

niktrs Over a year ago

Normally, RegionName column should not be NULL, blank or have any duplicates.

niktrs Over a year ago

Suggestion: @Scott maybe you should update the field with the data missing and then make a unique constraint.

Collectives™ on Stack Overflow

Displaying rows in SQL Server where COUNT = 0

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related