conditions on count in a select

Question

Hi, 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

Answer 1

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

Answer 2

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

Answer 3

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