CODEWITHSUNDEEP
sqlsql-servercount
user3262030
user3262030

Reputation: 87

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

Upvotes: 1

Views: 44

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

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

Upvotes: 3

Krisalay
Krisalay

Reputation: 75

SELECT 
    SUM(
        CASE type 
            WHEN 'B' THEN 0.5 
            WHEN 'A' THEN 1 
        END)
FROM <Table Name>
GROUP BY name

Upvotes: 2

Mifo
Mifo

Reputation: 553

You can use CASE to do a calculation like follows: SELECT SUM(CASE WHEN Type = 'A' THEN 1 ELSE 0 END) as SumA, SUM(CASE WHEN Type = 'B' THEN 0.5 ELSE 0 END) as Sum220 FROM Your Table

Upvotes: 0

Related Questions