CODEWITHSUNDEEP
sql
user3606175
user3606175

Reputation: 2193

How to get count values for multiple values in one column

My data is like this

Name Values
A    Val1
A    Val1
A    Val2
A    Val2   
A    Val2
B    Val1
B    Val2

I want to ouput my data is this way

Name Val1Count Val2Count
A    2         3
B    1         1

I can get the Name and count(*) for Val1 with this query. 

select [Name],count(*) FROM [table1]
where [values]='Val1'
group by [Name]

But I am not sure how to get the count(*) for val2 in the same row.

I tried doing this, but looks like this is not supported

select [name],@val1count= (above query for val1), @val2count = (above query for val2)

Please help. Thanks for looking.

Upvotes: 1

Views: 2273

Answers (1)

Steven
Steven

Reputation: 13769

This is called pivoting. Some databases provide a PIVOT function. However, you can also do this manually.

SELECT [Name], 
       SUM ( CASE WHEN [Values]='VAL1' THEN 1 ELSE 0 END ) AS Val1Count,
       SUM ( CASE WHEN [Values]='VAL2' THEN 1 ELSE 0 END ) AS Val2Count
  FROM [table1]
GROUP BY [Name]

Explanation:

  • The CASE WHEN ... END gives each row a "boolean" value for whether or not the row matches your condition.
  • The SUM ( ... ) counts the number of rows which returned "true" (or 1).
  • The GROUP BY [Name] consolidates the rows down to one row per distinct name.
  • If you add conditions to a WHERE clause, the CASE WHEN will only see the rows matching your WHERE conditions.

Upvotes: 1

Related Questions