Sums in MySQL

bravo14

Hi guys

I am trying to put a query together to show a summary of a teams sports results e.g.

Played Won Drawn Lost For Against Goal Difference Points

The query I have used so far is

SELECT Count (match_id) AS played, Sum(yardley_goals) AS for, Sum(opposition_goals) AS against, Sum(yardley_goals-opposition_goals) AS gd, Sum(points) AS pts
FROM tbl_fixtures
WHERE yardley_goals <>Null;

Any ideas where I am going wrong

If you need the table structur elet me know

Roger_Ramjet

You need to include a GROUP BY clause before you can use any of the aggregate functions like sum or count

Lars_Berg

You need to use the predicate is when checking for null.

SELECT Count (match_id) AS played, 
   Sum(yardley_goals) AS for, 
   Sum(opposition_goals) AS against, 
   Sum(yardley_goals-opposition_goals) AS gd, 
   Sum(points) AS pts
FROM tbl_fixtures
WHERE yardley_goals is not null

There is no need for a group by as there are no noneaggregated columns in the select list

Roger_Ramjet

Perfectly true, Lars, my bad 🙁

I spend my working days using Access and forget the mysql dialect. I ought to read the whole query as well 🙁

bravo14

Hi Lars berg

I have posted the following query

SELECT Count (match_id) AS played,
Sum(yardley_goals) AS for,
Sum(opposition_goals) AS against,
Sum(yardley_goals-opposition_goals) AS gd,
Sum(points) AS pts
FROM tbl_fixtures
WHERE yardley_goals is not null

and got the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for,
Sum(opposition_goals) AS against,
Sum(yardley_goals-opposition_go' at line 2

I am also unsure how to count how many are won, how many lost and how many drawn, it is one field of result

Roger_Ramjet

You need to familiarise yourself with Reserved Words as that is your error here.

bravo14

Thanks for that, simple when you know how, the only thing I am struggling with now is to count how many games are won, lost, and drawn, they are all stored in one field called result

Roger_Ramjet

You need to extract them out as separate items: either by using subqueries or possibly a union query

SELECT Count (match_id) AS played,
Sum(yardley_goals) AS for,
Sum(opposition_goals) AS against,
Sum(yardley_goals-opposition_goals) AS gd,
Sum(points) AS pts,
Sum(t1.won) AS won,
Sum(t2.lost) AS lost,
Sum(t3.drawn) AS drawn
FROM tbl_fixtures 
LEFT JOIN (SELECT match_id, 1 AS won FROM tbl_fixtures WHERE result = 'won') AS t1 USING(match_id)
LEFT JOIN (SELECT match_id, 1 AS lost FROM tbl_fixtures WHERE result = 'lost') AS t2 USING(match_id)
LEFT JOIN (SELECT match_id, 1 AS drawn FROM tbl_fixtures WHERE result = 'drawn') AS t3 USING(match_id)
WHERE yardley_goals is not null

Might get some syntax errors there, especially from summing NULL columns, but that is the general idea.

Using a union query would also work. I would do it in 2 queries because on the platforms I use every day a stored view would be more efficient.

vw_results:
SELECT *, 1 AS won, 0 AS lost, 0 AS drawn FROM tbl_fixtures WHERE result = 'won'
UNION 
SELECT *, 0 AS won, 1 AS lost, 0 AS drawn FROM tbl_fixtures WHERE result = 'lost'
UNION 
SELECT *, 0 AS won, 0 AS lost, 1 AS drawn FROM tbl_fixtures WHERE result = 'drawn';

final query:
SELECT Count (match_id) AS played,
Sum(yardley_goals) AS for,
Sum(opposition_goals) AS against,
Sum(yardley_goals-opposition_goals) AS gd,
Sum(points) AS pts,
Sum(won) AS won,
Sum(lost) AS lost,
Sum(drawn) AS drawn
FROM vw_results
WHERE yardley_goals is not null

I have given the 2 solutions as it is useful to know about different methods. Depending on circumstances one could choose the most appropriate solution: often just by benchmarking them to find the most efficient.

Using a UNION query as a view like that can be very useful if you are going to reuse it in several places. It also makes the code more maintainable by others as the sql is less complex, each part of the logic is clearly visible and modifications easier to debug.

bravo14

Thanks for this, I now have the desired results