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

1. Registered User
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. Registered User
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. Registered User
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
•