user3059460 user3059460 - 3 months ago 21
SQL Question

SQL: Distinct count with where?

Here is my table:

Players_Games_Table

MDATE PLAYER TEAM
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

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

select *
from
(
    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 s2.team = 'Liverpool')
group by player
having count(distinct team) > 1