CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
user1724435
user1724435

Reputation: 11

T-SQL: Count and Group By Across Multiple Columns

I have the following table:

Name | Col1 | Col2 | Col3
John   A      B      C
John   A      D      A
Bill   A      A      D
Bill   F      A      A
Steve  F      A      B
Steve  C      C      A

I want to know a total of how many As John, Bill, and Steve have...

e.g. John: 3, Bill: 4, Steve: 2

How could I do this using T-SQL? Thanks.

Upvotes: 1

Views: 1578

Answers (3)

Taryn
Taryn

Reputation: 247640

You can use the UNPIVOT function:

select name, count(val) TotalAs
from yourtable
unpivot
(
  val
  for col in (col1, col2, col3)
) u
where val = 'A'
group by name

See SQL Fiddle with Demo

Upvotes: 5

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

How about this for SQL Server 2012? It may be faster.

select name,sum(datalength(concat(col1,col2,col3))-
                datalength(replace(concat(col1,col2,col3),'A','')))
from yourtable
group by name;

Upvotes: 0

rs.
rs.

Reputation: 27427

Try this SQL Fiddle For below:

declare @table table (name varchar(10), c1 char(1), c2 char(1), c3 char(1))
insert into @table
SELECT 'John','A','B','C' UNION
SELECT 'John','A','D','A' UNION
SELECT 'Bill','A','A','D' UNION
SELECT 'Bill','F','A','A' UNION
SELECT 'Steve','F','A','B' UNION
SELECT 'Steve','C','C','A' 

; WITH CTE AS 
(
   SELECT name, 
       (SELECT Count(CASE WHEN val = 'A' Then 1 ELse Null end) 
        FROM (VALUES (c1),(c2),(c3)) AS value(val)) As ACOL
   from @table
)

SELECT Name, SUM(ACOL) ACount FROM CTE GROUP BY NAME

Upvotes: 2

Related Questions