CODEWITHSUNDEEP
sql-servercount
Luca Romagnoli
Luca Romagnoli

Reputation: 12475

conditions on count in a select

i have a table like this:

Table(MissioneID, Type)

Type can be 1,2 or 3

i have to count missions by type value:

ex. if table's content is:

MissioneID Type
1,1
1,2
1,1
2,3
1,2

The result of query is

MissioneID,Count1,Count2,Count3
1, 2,2,0
2,0,0,1

How can i do?

thanks

Upvotes: 0

Views: 407

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

select
    MissioneID,
    SUM(CASE WHEN [type]=1 THEN 1 ELSE 0 END) as Count1,
    SUM(CASE WHEN [type]=2 THEN 1 ELSE 0 END) as Count2,
    SUM(CASE WHEN [type]=3 THEN 1 ELSE 0 END) as Count3
from
    [Table]
group by
    MissioneID

Upvotes: 4

Aaronaught
Aaronaught

Reputation: 122674

Looks like you're trying to do a pivot query here:

SELECT MissioneID, [1], [2], [3]
FROM Table
PIVOT
(
    COUNT(Type)
    FOR Type IN ([1], [2], [3])
) AS pvt

Upvotes: 2

Axarydax
Axarydax

Reputation: 16623

select type,count(*)
from table
group by MissioneID

Upvotes: 0

Related Questions