How to return empty groups in SQL GROUP BY clause

Question

I have a query to return how much is spent on-contract and off-contract at each location, that returns something like this:

Location     | ContractStatus | Expenses
-------------+----------------+---------
New York     | Ad-hoc         | 2043.47
New York     | Contracted     | 2894.57
Philadelphia | Ad-hoc         | 3922.53
Seattle      | Contracted     | 2522.00

The problem is, I only get one row for locations that are all ad-hoc or all contracted expenses. I'd like to get two rows back for each location, like this:

Location     | ContractStatus | Expenses
-------------+----------------+---------
New York     | Ad-hoc         | 2043.47
New York     | Contracted     | 2894.57
Philadelphia | Ad-hoc         | 3922.53
Philadelphia | Contracted     |    0.00
Seattle      | Ad-hoc         |    0.00
Seattle      | Contracted     | 2522.00

Is there any way I can accomplish this through SQL? Here is the actual query I'm using (SQL Server 2005):

SELECT Location, 
     CASE WHEN Orders.Contract_ID IS NULL 
          THEN 'Ad-hoc' ELSE 'Contracted' END 
                     AS ContractStatus,
     SUM(OrderTotal) AS Expenses
FROM Orders
GROUP BY Location, 
    CASE WHEN Orders.Contract_ID IS NULL 
         THEN 'Ad-hoc' ELSE 'Contracted' END
ORDER BY Location ASC, ContractStatus ASC

Answer 1

You can use COALESCE to return 0 for NULL values:

SELECT Orders.Location
 , (CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) AS   ContractStatus
 , COALESCE(SUM(Orders.OrderTotal), 0.00) AS Expenses
FROM Orders
GROUP BY Orders.Location
   , (CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END)
ORDER BY Orders.Location ASC, ContractStatus ASC

Answer 2

You have to somehow create at least one row of each status for each location before grouping. Here's one way:

SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses
FROM
(
    SELECT
        Location,
        CASE
            WHEN Contract_ID IS NULL THEN 'Ad-hoc'
            ELSE 'Contracted'
        END AS ContractStatus,
        SUM(OrderTotal) AS Expenses
    FROM Orders
    GROUP BY
        Location,
        CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END)
) o
RIGHT JOIN
(
    SELECT DISTINCT o.Location, s1.ContractStatus
    FROM Orders o
    CROSS JOIN
    (
        SELECT 'Ad-hoc' AS ContractStatus
        UNION ALL
        SELECT 'Contracted'
    ) s1
) s
ON s.Location = o.Location
AND s.ContractStatus = o.ContractStatus

Edit: I'm not sure about the performance of that DISTINCT/CROSS JOIN combo, but if it's a one-time or infrequently-used query it should be OK.

Answer 3

Yes, construct an expression that returns the ordertotal for adhoc only, and 0 for the others, and another one that does the opposite, and sum those expressions. This will include one row per location with two columns one for adhoc and one for Contracted...

 SELECT Location,  
     Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) AdHoc,
     Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal  End) Contracted
 FROM Orders 
 GROUP BY Location

if you reallly want separate rows for each, then one approach would be to:

 SELECT Location, Min('AdHoc') ContractStatus,
     Sum(Case When Contract_ID Is Null 
              Then OrderTotal Else 0 End) OrderTotal
 FROM Orders 
 GROUP BY Location
 Union
 SELECT Location, Min('Contracted') ContractStatus,
     Sum(Case When Contract_ID Is Null 
              Then 0 Else OrderTotal  End) OrderTotal
 FROM Orders 
 GROUP BY Location
 Order By Location

Answer 4

You need to populate a list of distinct locations and contracts first. See this query, it might have some syntax errors as I didn't run it.

;WITH Locations AS 
(
    SELECT DISTINCT Location, 1 as ContractStatus
    FROM    Orders 
    UNION 
    SELECT DISTINCT Location, 2 as ContractStatus
    FROM    Orders
)

SELECT  Location, 
        CASE 
            WHEN L.ContractStatus = 1 THEN 'Ad-hoc'
            ELSE 'Contracted'
        END, 
        ISNULL(SUM(OrderTotal),0) Expenses
FROM    Locations L
    LEFT JOIN Orders O 
        ON L.Location = O.Location AND L.ContractStatus = CASE WHEN O.Contract_ID IS NULL THEN 1 ELSE 2 END
GROUP BY    L.Location, 
            CASE 
                WHEN L.ContractStatus = 1 THEN 'Ad-hoc'
                ELSE 'Contracted'
            END