CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bypivot
T D
T D

Reputation: 519

Summarize value counts per value in 1 column in SQL Server

Data:

name | car color
-----+---------
mike | green
mike | blue
mike | red
bob  | green
bob  | red
tom  | blue

Desired result:

name | green count | blue count | red count
-----+-------------+------------+---------
mike |      1      |      1     |    1
bob  |      1      |      0     |    1
tom  |      0      |      1     |    0

I was thinking about running several subqueries and left joining such as:

select name, count(*) as count blue
from table
where car color = 'blue'

etc

Thank you

Upvotes: 0

Views: 88

Answers (2)

Jeremy Hodge
Jeremy Hodge

Reputation: 682

If you know the colors at design time, you can use a pivot.

DECLARE @t TABLE
(
    personName NVARCHAR(16) NOT NULL,
    carColor NVARCHAR(16) NOT NULL
);

INSERT INTO @t
(
    personName,
    carColor
)
VALUES
('mike', 'green'),
('mike', 'blue'),
('mike', 'red'),
('bob', 'green'),
('bob', 'red'),
('tom', 'blue');

SELECT t.personName,
       t.red,
       t.blue,
       t.green
FROM
(
    SELECT pvt.*
    FROM @t t
        PIVOT
        (
            COUNT(carColor)
            FOR carColor IN ([green], [blue], [red])
        ) pvt
) AS t;

Output:

bob     1   0   1
mike    1   1   1
tom     0   1   0

Upvotes: 2

GMB
GMB

Reputation: 222722

You can use conditional aggregation:

SELECT 
    name,
    SUM(CASE WHEN car_color = 'green' THEN 1 ELSE 0 END) green_count,
    SUM(CASE WHEN car_color = 'blue' THEN 1 ELSE 0 END) blue_count,
    SUM(CASE WHEN car_color = 'red' THEN 1 ELSE 0 END) red_count
FROM mytable 
GROUP BY name

Upvotes: 4

Related Questions