user3262030 user3262030 - 6 months ago 8
SQL Question

How can I get the count depending on the column value in SQL Server

I have this table in SQL Server

name type date
aaa A 2016-05-05
aaa A 2016-05-22
aaa B 2016-05-21
bbb A 2016-05-15
bbb B 2016-05-01


and i want to make a query to get this result

name count(type)
aaa 2.5
bbb 1.5


NB : for A the count must increase with 1, and for B with 0.5 because I have this rule :

count(type)=count(A)+count(B)/2

Answer

Try this:

SELECT SUM(CASE type WHEN 'A' THEN 1.0 WHEN 'B' THEN 0.5 END)
FROM mytable
GROUP BY name
Comments