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

[mbza]

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.

[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.