user3059460 user3059460 - 1 year ago 83
SQL Question

SQL: Distinct count with where?

Here is my table:


12 evra liverpool
12 giggs liverpool
12 smith liverpool
13 evra leeds
13 giggs liverpool
13 smith manu
14 evra spurs
14 giggs liverpool
14 smith chelsea

I want to return the players name (PLAYER) of players who have played for "liverpool" and at least one other team.

Something like this:

select distinct player, count(team) from stats
where team = 'liverpool'
group by player
having count(team) > 1;

Answer Source

Can be done in several ways, for example you can have a correlated sub-query that performs the team counting:

select *
    select player, (select count(distinct team) from stats s2
                    where s2.player = s1.player) as teamcount
    from stats s1
    where team = 'Liverpool'
) dt
where teamcount > 1

Or, a standard GROUP BY, combined with EXISTS:

select player, count(distinct team)
from stats s1
where exists (select * from stats s2
              where s1.player = s2.player
                and = 'Liverpool')
group by player
having count(distinct team) > 1