Using SUM with multiple joins in mysql

Question

I've been looking for a solution to this, there's plenty of similar questions but none have any proper answers that helped me solve the problem.

First up, my questions/problem:

I want to sum and count certain columns in a multiple join query
Is it not possible with multiple joins? Do I have to nest SELECT queries?

Here's a SQL dump of my database with sample data: http://pastie.org/private/vq7qkfer5mwyraudb5dh0a

This is the query I thought would do the trick:

SELECT firstname, lastname, sum(goal.goal), sum(assist.assist), sum(gw.gw), sum(win.win), count(played.idplayer) FROM player
LEFT JOIN goal USING (idplayer)
LEFT JOIN assist USING (idplayer)
LEFT JOIN gw USING (idplayer)
LEFT JOIN win USING (idplayer)
LEFT JOIN played USING (idplayer)
GROUP BY idplayer

What I'd like this to produce is a table where the columns for goal, assist, gw, win and played are a sum/count of every row in that column, like so: (with supplied sample data)

+-----------+----------+------+--------+----+-----+--------+
| firstname | lastname | goal | assist | gw | win | played |
+-----------+----------+------+--------+----+-----+--------+
| Gandalf   | The White|   10 |      6 |  1 |   1 |      2 |
| Frodo     | Baggins  |   16 |      2 |  1 |   2 |      2 |
| Bilbo     | Baggins  |    7 |      3 |  0 |   0 |      2 |
+-----------+----------+------+--------+----+-----+--------+

So, to iterate the above questions again, is this possible with one query and multiple joins?

If you provide solutions/queries, please explain them! I'm new to proper relational databases and I have never used joins before this project. I'd also appreciate if you avoid aliases unless necessary.

I have run the above query without sum and grouping and I get a set of rows for each column I do a SELECT on, which I suspect is then multiplied or added together, but I was under the impression that grouping and/or doing sum(TABLE.COLUMN) would solve that.

Another thing is that, I think, doing a SELECT DISTINCT or any other DISTINCT operation won't work since that will leave out some ("duplicate") results.

PS. If it matters, my dev machine is a WAMP but release will be on ubuntu/apache/mysql/php.

You have spent considerable time on the DML, so please create an sqlfiddle. — ron tornambe
– ron tornambe, Commented Jan 2, 2013 at 1:38
I did not know about sqlfiddle, ic3be3rg has created one in his answer though so I won't now. — Nahaz
– Nahaz, Commented Jan 2, 2013 at 10:43

ic3b3rg · Accepted Answer · 2013-01-02 01:54:10Z

12

To understand why you're not getting the answers you expect, take a look at this query:

SELECT * FROM player LEFT JOIN goal USING (idplayer)

As you can see, the rows on the left are duplicated for the matching rows on the right. That procedure is repeated for each join. Here's the raw data for your query:

SELECT * FROM player
LEFT JOIN goal USING (idplayer)
LEFT JOIN assist USING (idplayer)
LEFT JOIN gw USING (idplayer)
LEFT JOIN win USING (idplayer)
LEFT JOIN played USING (idplayer)

Those repeated values are then used for the SUM calculations. The SUMs need to be calculated before the rows are joined:

SELECT firstname, lastname, goals, assists, gws, wins, games_played
FROM player
INNER JOIN 
  (SELECT idplayer, SUM(goal) AS goals FROM goal GROUP BY idplayer) a
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(assist) AS assists FROM assist GROUP BY idplayer) b
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(gw) AS gws FROM gw GROUP BY idplayer) c
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, SUM(win) AS wins FROM win GROUP BY idplayer) d
  USING (idplayer)
INNER JOIN
  (SELECT idplayer, COUNT(*) AS games_played FROM played GROUP BY idplayer) e
  USING (idplayer)

SQLFiddle

answered Jan 2, 2013 at 1:54

ic3b3rg

15k4 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nahaz Over a year ago

It was as I suspected then! Thanks for the quick reply. I'll fiddle around with the query to understand it better :)

Gherman Over a year ago

If you calculate sum and then join, doesn't it mean that some calculated sums will be discarded after being calculated? I mean, won't it calculate unneeded sums?

Collectives™ on Stack Overflow

Using SUM with multiple joins in mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related