List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:June 14 1999 10:18pm
Subject:Re: COUNT DISTINCT GROUP BY (help!)
View as plain text  
On So, 1999-06-13 21:59:13 -0500, Thomas P. Meyer wrote:
> My goal is to write a query which lists in descending order the
> number of different team members a player has had.   For the
> above example, it would look like:
> 
> PlayerID    count
> 10                3
> 11                2
> 12                2
> 13                1

First think about how to get pairs of team members:

SELECT DISTINCT
        a.PlayerID AS player, b.PlayerID AS playmate
FROM
        entrant AS a, entrant AS b
WHERE
        a.ID = b.ID
        AND a.PlayerID != b.PlayerID
ORDER BY
        a.PlayerID, b.PlayerID
;

Now just group the result by player and count playmate.

First try (wrong):

SELECT DISTINCT
        a.PlayerID AS player, COUNT(b.PlayerID) AS count
FROM
        entrant AS a, entrant AS b
WHERE
        a.ID = b.ID
        AND a.PlayerID != b.PlayerID
GROUP BY
        player
ORDER BY
        count DESC
;

Problem: DISTINCT only works on the final result set.  So the
         duplicates are still there when grouping comes into effect ...

So what we really need is:

SELECT
        a.PlayerID AS player, COUNT(DISTINCT b.PlayerID) AS count
FROM
        entrant AS a, entrant AS b
WHERE
        a.ID = b.ID
        AND a.PlayerID != b.PlayerID
GROUP BY
        player
ORDER BY
        count DESC
;

Unfortunately MySQL doesn't support COUNT DISTINCT yet, so one has to use a
temporary table:

1. CREATE TABLE pairs ( player INT, playmate INT );
   
2. INSERT INTO pairs
       SELECT DISTINCT a.PlayerID AS player, b.PlayerID AS playmate
       FROM            entrant AS a, entrant AS b
       WHERE           a.ID = b.ID
                       AND a.PlayerID != b.PlayerID;
   
3. SELECT   player, COUNT(playmate) AS count
   FROM     pairs
   GROUP BY player
   ORDER BY count DESC;
   
4. DROP TABLE pairs;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
COUNT DISTINCT GROUP BY (help!)Thomas P. Meyer14 Jun
  • Re: COUNT DISTINCT GROUP BY (help!)Adam Gotheridge14 Jun
  • Re: COUNT DISTINCT GROUP BY (help!)Garrick Staples14 Jun
  • Re: COUNT DISTINCT GROUP BY (help!)Garrick Staples14 Jun
  • Re: COUNT DISTINCT GROUP BY (help!)Martin Ramsch15 Jun