Using SUM with multiple joins in mysql

Question

I was looking for a solution to this, there are many similar questions, but no one has the correct answers that helped me solve the problem.

First, my questions / concerns:

• I want to sum and count some columns in a multiple join request
• Is this not possible with multiple unions? Do I need to SELECT queries?

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

This is a query that 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 would like to create is a table in which the columns for goal, support, gw, win and play are the sum / quantity of each row in this column, for example: (with provided 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 repeat the above questions, is this possible with a single query and multiple joins?

If you provide solutions / requests , please explain them! I am new to the right relational databases, and I never used joins before this project. I would also appreciate if you avoided the aliases if necessary.

I executed the above query without summing and grouping, and I get a rowset for each column that I do SELECT on, which I suspect is then multiplied or added together, but I got the impression that grouping and / or doing sum(TABLE.COLUMN) will solve this.

Another thing is that, in my opinion, performing a SELECT DISTINCT or any other DISTINCT operation will not work, as this will not produce results ("duplicates").

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

Answer 1

To understand why you are not getting the answers you expect, look at this query:

 SELECT * FROM player LEFT JOIN goal USING (idplayer) 

As you can see, the lines on the left are duplicated for the corresponding lines on the right. This procedure is repeated for each connection. Here is the raw data for your request:

 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) 

These duplicate values ​​are then used for SUM calculations. SUM must be calculated before concatenating the lines:

 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