Clever Geek Handbook

SQL selection 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

Is it done 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 cannot make out the syntax to do this in one. TIA for your help. John

+3


source to share


3 answers


You can do conditional aggregation with case

:



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


source


Try the following:

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

+1


source


you can do it by group

select team, score (name) as players, beat (win) winners (select team, name, amount (excellent won), test team by team, name) group by team

+1


source






More articles:


All Articles