Kirk Fleming Kirk Fleming - 1 month ago 14
SQL Question

Multi-column Conditional Aggregation

In SQL Server 2008.

I have things that have components in one of two states, and the table looks like this:

create table Things (
ThingName varchar(10),
ItemNumber INT,
ItemStatus varchar(10));

INSERT INTO Things (
ThingName,
ItemNumber,
ItemStatus)
VALUES
('a', 1, 'red'),
('a', 2, 'red'),
('a', 3, 'blue'),
('b', 1, 'red'),
('b', 2, 'red'),
('b', 3, 'red'),
('b', 4, 'red'),
('c', 1, 'blue'),
('c', 2, 'blue'),
('c', 3, 'red');


The result I need for each Thing is
1) total number of items
2) total red items
3) total blue items

Result would look like:

ThingName TotalItems RedItems BlueItems
a 3 2 1
b 4 4 0
c 3 1 2


The 'obvious' query I use to do this:

SELECT
ThingName,
sum(Red + Blue) as TotalItems,
sum(Red) as RedItems,
sum(Blue) as BlueItems
FROM (
SELECT
ThingName,
case
when ItemStatus = 'red' then count(*)
else 0
end as Red,
case
when ItemStatus = 'blue' then count(*)
else 0
end as Blue
FROM Things
GROUP BY
ThingName,
ItemStatus) a GROUP BY ThingName;


This works, but seems primitive and unsatisfying. Actually, it just seems like I am failing to see how to aggregate as needed without resorting to a two-step approach. Suggestions?

Answer

You can simplify things using conditional aggregation:

SELECT
    ThingName,
    count(ItemNumber) as TotalItems,
    count(case when ItemStatus='Red' then ItemNumber  end) as RedItems,
    count(case when ItemStatus='Blue' then ItemNumber  end) as BlueItems
FROM Things
GROUP BY ThingName;

Hence, instead of using a subquery that uses a CASE expression to get count of Total, Red, Blue items, use the CASE expression directly inside the aggregate function, COUNT in this case.

Demo here