Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Complex query - need help (where a OR b, SUM() etc)

    prizes
    +------------+------------+---------+---------+
    | l_playerid | s_playerid | l_prize | s_prize |
    +------------+------------+---------+---------+
    | 1 | 1 | 500 | 350 |
    | 2 | 1 | 650 | 245 |
    | 1 | 2 | 500 | 65 |
    | 2 | 2 | 150 | 85 |
    | 3 | 3 | 95 | 125 |
    | 5 | 2 | 350 | 205 |
    +------------+------------+---------+---------+

    players
    +----------+----------+
    | playerid | name |
    +----------+----------+
    | 1 | Bob |
    | 2 | Jay |
    | 3 | Suzie |
    | 4 | David |
    | 5 | Rick |
    | 6 | Jane |
    +----------+----------+



    I need:

    +-------+----------------+----------------+
    | name | total_l_prizes | total_s_prizes |
    +-------+----------------+----------------+
    | Bob | 1000 | 595 |
    | Jay | 800 | 355 |
    | Suzie | 95 | 125 |
    | Rick | 350 | 0 |
    +-------+----------------+----------------+



    attempt:

    SELECT players.name, SUM(prizes.l_prize) AS total_l_prizes,
    SUM(prizes.s_prize) AS total_s_prizes FROM prizes, players WHERE
    players.playerid = prizes.l_playerid OR players.playerid =
    prizes.s_playerid GROUP BY players.playerid

    HOWEVER, player gets awarded both l and s prizes for each record even
    if he/she only appears as l OR s player for that record.

    +-------+----------------+----------------+
    | name | total_l_prizes | total_s_prizes |
    +-------+----------------+----------------+
    | Bob | 1650 | 660 |
    | Jay | 1650 | 600 |
    | Suzie | 95 | 125 |
    | Rick | 350 | 205 |
    +-------+----------------+----------------+


    Thanks,
    Rey

  2. #2
    Join Date
    Apr 2004
    Posts
    4
    table and result clearification


    prizes (l_playerid, s_playerid, l_prize, s_prize)
    1 - 1 - 500 - 350
    2 - 1 - 650 - 245
    1 - 2 - 500 - 65
    2 - 2 - 150 - 85
    3 - 3 - 95 - 125
    5 - 2 - 350 - 205

    players (playerid, name)
    1 - Bob
    2 - Jay
    3 - Suzie
    4 - David
    5 - Rick
    6 - Jane

    desired result (name, total_l_prizes, total_s_prizes)
    Bod - 1000 - 595
    Jay - 800 - 355
    Suzie - 95 - 125
    Rick - 350 - 0

    1st attempt result (name, total_l_prizes, total_s_prizes) - incorrect
    Bod - 1650 - 600
    Jay - 1650 - 600
    Suzie - 95 - 125
    Rick - 350 - 205

    SELECT players.name, SUM(prizes.l_prize) AS total_l_prizes, SUM(prizes.s_prize) AS total_s_prizes FROM prizes, players WHERE players.playerid = prizes.l_playerid OR players.playerid = prizes.s_playerid GROUP BY players.playerid

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Re: Complex query - need help (where a OR b, SUM() etc)

    SELECT players.name,
    SUM(case when prizes.l_playerid = players.playerid then prizes.l_prize else 0 end) AS total_l_prizes,
    SUM(case when prizes.s_playerid = players.playerid then prizes.s_prize else 0 end) AS total_s_prizes
    FROM prizes , players
    WHERE players.playerid = prizes.l_playerid OR players.playerid = prizes.s_playerid
    GROUP BY players.name
    ORDER BY players.name

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •