Multi-column Conditional Aggregation

Question

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 1

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

Answer 2

Also possible with sum:

SELECT
      ThingName,
      COUNT(*) as TotalItems,
      SUM(CASE ItemStatus WHEN 'Red' THEN 1 ELSE 0 END) AS RedItems,
      SUM(CASE ItemStatus WHEN 'Blue' THEN 1 ELSE 0 END) AS BlueItems
FROM Things
GROUP BY ThingName;