Условная агрегация с несколькими столбцами

Question

В SQL Server 2008

У меня есть вещи, которые имеют компоненты в одном из двух состояний, и таблица выглядит так:

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');

Результат, который мне нужен для каждой вещи: 1) общее количество предметов 2) общее количество красных предметов 3) общее количество синих предметов

Результат будет выглядеть так:

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

"Очевидный" запрос, который я использую для этого:

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;

Это работает, но кажется примитивным и неудовлетворительным. На самом деле, мне кажется, что я не вижу, как агрегировать по мере необходимости, не прибегая к двухэтапному подходу. Предложения?

Answer 1

Вы можете упростить вещи с помощью условного агрегирования:

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;

Следовательно, вместо использования подзапроса, который использует CASE выражение, чтобы получить количество элементов Total, Red, Blue, используйте CASE выражение непосредственно внутри агрегатной функции, COUNT в этом случае.

Демо здесь

Answer 2

Также возможно с 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;