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

Charles Bretana · Accepted Answer · 2009-12-30T15:38:38.707

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

edited Dec 30 '09 at 15:38

answered Dec 30 '09 at 15:28

Charles Bretana

143,358
22
150
216

Thanks, this works. Unfortunately I do need separate rows because I'm going through a reporting package that draws graphs one row at at time. :( – Jenni Dec 30 '09 at 16:23
What is the `Min('AdHoc') ContractStatus` for? Isn't `Min()` for numeric values? The query works just as well with `'AdHoc' AS ContractStatus` – Jenni Dec 30 '09 at 16:25
I wasn;t sure it would, as normally any output column in a Group By Query that is not in the Group By clause must use an Aggregate function... I guess that rule doesn't apply to columns which are generated from a constant value (like 'ADHOC') – Charles Bretana Dec 30 '09 at 17:06

Aaronaught · Answer 2 · 2009-12-30T17:07:14.830

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.

something is wrong with this. for each city, i get a row for each ad-hoc total and each contracted total for every other city. in other words, I get: NY Ad-hoc $2043, NY Ad-hoc $3922, NY Contr. $2894, NY Contr. $2522. And I get the same four rows for each city. — Jenni, Dec 30 '09 at 16:35
Whoops, I forgot one of the join conditions. I updated the answer. — Aaronaught, Dec 30 '09 at 17:07

score 0 · Answer 3 · answered Dec 30 '09 at 15:53

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

fyi- it gives an error on the `SELECT Location` line. it needs to be `SELECT L.Location`. — Jenni, Dec 30 '09 at 16:30

score 0 · Answer 4 · answered Sep 21 '21 at 14:24

Most of the previous solutions were dependent on knowing exactly how many of the second column there were, and though I'm far from a SQL expert, I had a use case where the number of ContractStatus was unknown for each Location.

SO I found How to merge 2 columns with all possible combinations in SQL? which showed how to handle any number of distinct secondary metrics.

http://sqlfiddle.com/#!18/72236b/3/0

CREATE TABLE Table1
    ([Location] varchar(12), [ContractStatus] varchar(10), [Expenses] int)
;
    
INSERT INTO Table1
    ([Location], [ContractStatus], [Expenses])
VALUES
    ('New York', 'Ad-hoc', 2043.47),
    ('New York', 'Contracted', 2894.57),
    ('Philadelphia', 'Ad-hoc', 3922.53),
    ('Seattle', 'Contracted', 2522.00)
;

-- Use CTE to get all possibilities
with possibilities as (
    select l.Location, c.ContractStatus
    from (select distinct Location from [Table1]) l cross join
         (select distinct ContractStatus from [Table1]) c
)
select
    p.Location
    ,p.ContractStatus
    -- Choose the proper agg function here
    ,sum([Expenses]) as Expenses
from possibilities p 
left join [Table1] t on p.Location = t.Location and p.ContractStatus = t.ContractStatus
group by p.Location, p.ContractStatus
order by Location, ContractStatus

How to return empty groups in SQL GROUP BY clause

4 Answers4

Linked