How to get count values for multiple values in one column

Question

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.

Steven · Accepted Answer · 2014-06-19 16:12:19Z

1

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.

edited Jun 19, 2014 at 16:12

answered Jun 18, 2014 at 19:19

Steven

13.9k27 gold badges105 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3606175 Over a year ago

If I had other clauses to add along with [values]='', Would they go in the Case WHen statement as well? These extra clauses are same for both the cases. Like product ='', Date=''

user3606175 Over a year ago

Since they are common for both cases, I added them after from [table1] where product= ''. But looks like its not working.

Steven Over a year ago

I edited my answer with an explanation. Hopefully, that helps.

user3606175 Over a year ago

Actually. The original query with where clause was right. I missed a clause and that's why the numbers were not matching. Thanks so much for the help. :)

user3606175 Over a year ago

Btw. I used SUM instead of COUNT. Thanks

Collectives™ on Stack Overflow

How to get count values for multiple values in one column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related