RDowns RDowns - 21 days ago 6
MySQL Question

Correct MySQL syntax for the SUM of multiple COLUMNS where multiple WHERE exists?

I have the following database table:

+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| results_id | int(11) | NO | PRI | NULL | auto_increment |
| community_id | int(11) | NO | | NULL | |
| player1_id | int(11) | NO | | NULL | |
| player1_name | varchar(50) | NO | | NULL | |
| player1_team | varchar(50) | NO | | NULL | |
| player1_goals | int(11) | NO | | NULL | |
| player1_result | varchar(3) | NO | | NULL | |
| player2_goals | int(11) | NO | | NULL | |
| player2_result | varchar(3) | NO | | NULL | |
| player2_id | int(11) | NO | | NULL | |
| player2_name | varchar(50) | NO | | NULL | |
| player2_team | varchar(50) | NO | | NULL | |
| player1_pts | int(11) | NO | | NULL | |
| player2_pts | int(11) | NO | | NULL | |
| date | date | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+


I want to run a MySQL query that will tell me who a player has scored the most goals against.

That player can either be player1 or player2.

I imagine it being something like this:

select member, goals, count(*) Total
from
(
select player2_name as member, player1_goals as goals
from results
WHERE player1_id = 2
union all
select player1_name as member, player2_goals as goals
from results
WHERE player2_id = 2

) AS T

group by goals
order by Total desc
Limit 1


This gives a result:

member | goals | Total
Jamie Charles | 2 | 11
Jamie Charles | 0 | 8
Jamie Charles | 3 | 5
Jamie Charles | 1 | 4
Jamie Charles | 5 | 1


But it looks like it is saying how many of that number of goals has occurred. So the correct result would be
(2x11)+(3*5)+(1*4)+(5*1)


What is the correct Syntax for this query?

Answer

You need to group by member, use the sum aggregate function to get the sum of all the goals. You do not need the order

select member, sum(goals), count(*) Total_goal_records
from 
(
select player2_name as member, player1_goals as goals
from results
WHERE player1_id = 2
union all
select player1_name as member, player2_goals as goals
from results
WHERE player2_id = 2 

) AS T

group by member 
order by Total desc;