CODEWITHSUNDEEP
sqlsql-server
John Harbour
John Harbour

Reputation: 33

SQL Select with Distinct

With this data

Team Name Won
X    Andy  1
X    Cara  1
X    Cara  1
X    Eric  0
X    Eric  0
X    Eric  0
X    Eric  0
Y    Bill  0
Y    Dave  0
Y    Dave  1
Y    Dave  1

I want for each team the number of players and the number of winners

Team Players Winners
X       3       2
Y       2       1

Does it in two queries:

select team, count(distinct Name) as Players from test group by team
select team, count(distinct Name) as Winners from test where Won=1 group by team

but I can't work out the syntax to do it in one. TIA for your help. John

Upvotes: 3

Views: 54

Answers (3)

Carsten Massmann
Carsten Massmann

Reputation: 28236

Try this:

select team, count(distinct Name) as Players, sum(won) Winners 
from (select distinct * from test) tst 
group by team

You can test it here: http://rextester.com/QATLK24197

Upvotes: 1

Rams
Rams

Reputation: 2159

you can do it by group by

select team,count(name) as players ,sum(won) winners from ( select team, Name,sum(distinct won) as won from test group by team,name ) group by team

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

You can do conditional aggregation using case:

select team,
    count(distinct name) as Players,
    count(distinct case when won = 1 then name end) as winners
from test
group by team

Upvotes: 3

Related Questions