471,092 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Hello, Trying to sum() two query's.

Hi all, I'm trying to setup a database (mysql 4.0) for my snooker club
and have run into a little problem, being new to myslq and php. I want
to setup a table where all the results will go, then query this table to
get players averages etc. So will look something like this.

| score | h_playerid | a_playerid | h_score | a_score |
+-------+------------+------------+---------+---------+
| 1 | 1 | 2 | 20 | 15 |
| 2 | 1 | 2 | 35 | 25 |
| 3 | 2 | 1 | 5 | 25 |
| 4 | 2 | 1 | 55 | 25 |

as you can see player play home and away tried to use the following query,

mysql> (select h_playerid, sum(h_score), sum(a_score) from games group
by h_playerid)
-> union all
-> (select a_playerid, sum(a_score), sum(h_score) from games group
by a_playerid)
-> order by h_playerid;

which gives me a result of:

+------------+--------------+--------------+
| h_playerid | sum(h_score) | sum(a_score) |
+------------+--------------+--------------+
| 1 | 55 | 40 |
| 1 | 50 | 60 |
| 2 | 60 | 50 |
| 2 | 40 | 55 |
+------------+--------------+--------------+

half way there now how do I add the home and away entries together? have
tried a GROUP BY at the end no joy, sysntax error;

ERROR 1064: 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 'group by h_playerid' at line 4

Thanks to all who could send me in the right direction.


Jul 20 '05 #1
1 1510
HpO
Howdy,

if you're wanting the average (of all games, home and away) for each player,
here's a way to do it in a single query:

mysql> select
-> g1.h_player as player ,
-> (
-> sum(g1.h_score) +
-> (
-> select
-> sum(a_score)
-> from games
-> where a_player = g1.h_player
-> )
-> ) /
-> (
-> count(g1.game) +
-> (
-> select
-> count(game)
-> from games
-> where a_player = g1.h_player
-> )
-> ) as avg_of_scores
-> from games g1
-> group by g1.h_player;
+--------+---------------+
| player | avg_of_scores |
+--------+---------------+
| 1 | 26.25 |
| 2 | 25.00 |
+--------+---------------+
2 rows in set (0.01 sec)

Hope this helps...

"mbza" <mb**@blueyonder.co.uk> wrote in message
news:nu*******************@fe1.news.blueyonder.co. uk...
Hi all, I'm trying to setup a database (mysql 4.0) for my snooker club and
have run into a little problem, being new to myslq and php. I want to
setup a table where all the results will go, then query this table to get
players averages etc. So will look something like this.

| score | h_playerid | a_playerid | h_score | a_score |
+-------+------------+------------+---------+---------+
| 1 | 1 | 2 | 20 | 15 |
| 2 | 1 | 2 | 35 | 25 |
| 3 | 2 | 1 | 5 | 25 |
| 4 | 2 | 1 | 55 | 25 |

as you can see player play home and away tried to use the following query,

mysql> (select h_playerid, sum(h_score), sum(a_score) from games group by
h_playerid)
-> union all
-> (select a_playerid, sum(a_score), sum(h_score) from games group by
a_playerid)
-> order by h_playerid;

which gives me a result of:

+------------+--------------+--------------+
| h_playerid | sum(h_score) | sum(a_score) |
+------------+--------------+--------------+
| 1 | 55 | 40 |
| 1 | 50 | 60 |
| 2 | 60 | 50 |
| 2 | 40 | 55 |
+------------+--------------+--------------+

half way there now how do I add the home and away entries together? have
tried a GROUP BY at the end no joy, sysntax error;

ERROR 1064: 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
'group by h_playerid' at line 4

Thanks to all who could send me in the right direction.


Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Chris | last post: by
1 post views Thread by Access | last post: by
3 posts views Thread by Joost | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.