Is there a way to count the number of values in column?-CodePudding

I am a newbie studying MSSQL and Database. I am trying to write a SQL query to count values in the column. Following table is original one.

name value
----------
  A    1
  A    1
  A    2
  B    1
  B    2

I want to get a table like this.

name  one  two
--------------
  A    2    1
  B    1    1

A has two 1 and one 2 and B has one 1 and 2. It seems I can accomplish it using COUNT built-in function. I tried but failed. Is there any idea to do it?

CodePudding user response：

use conditional aggregation

select name, sum(case when value=1 then 1 else 0 end) as one,
         sum(case when value=2 then 1 else 0 end) as two
from table_name group by name

CodePudding user response：

If you have multiple values on your table, then you have to mention additional case statements as per the advisable from Zaynul