SQL Select with Distinct

Question

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

Gurwinder Singh · Accepted Answer · 2017-03-25 17:15:02Z

3

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

answered Mar 25, 2017 at 17:15

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Harbour Over a year ago

Excellent. I managed to come up with an answer using four linked CTEs: this gives the same result set and is MUCH simpler. I had no idea a case statement could be written without an else!

Rams · Accepted Answer · 2017-03-25 17:19:47Z

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

Carsten Massmann · Accepted Answer · 2017-03-25 17:27:04Z

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

Collectives™ on Stack Overflow

SQL Select with Distinct

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related