Nick1181
asked on
MySQL complex Sum query
How do you write
SELECT team, SUM(goals) FROM gameResults GROUP BY team
where gameResults also has a date field and you only want to sum the last 3 games?
gameResults is a table with
team
goals
date
Putting LIMIT at the end isn't quite right - what's needed is a sort of LIMIT within the SUM() funtion.
It seems like it should be simple to 'sum the goals from the last 3 games' but even doing it in php code is tending towards loopy fiddelyness.
Any help much appreciated.
SELECT team, SUM(goals) FROM gameResults GROUP BY team
where gameResults also has a date field and you only want to sum the last 3 games?
gameResults is a table with
team
goals
date
Putting LIMIT at the end isn't quite right - what's needed is a sort of LIMIT within the SUM() funtion.
It seems like it should be simple to 'sum the goals from the last 3 games' but even doing it in php code is tending towards loopy fiddelyness.
Any help much appreciated.
Hmmm I may have an older mysql version, but it doesn't support inner selects.
You may want to select first the last 3 games into a temporary, and then get their sum.
In PostgreSQL you can do something like this (assuming you have a table called t2, with 2 integer fields, t1 and t2):
select sum(t1) from (select * from t2 order by t2 limit 3) as abc;
or like this:
select sum(t1) from t2 where t2.t2 in(select t2 from t2 order by t2 limit 3) ;
You may want to select first the last 3 games into a temporary, and then get their sum.
In PostgreSQL you can do something like this (assuming you have a table called t2, with 2 integer fields, t1 and t2):
select sum(t1) from (select * from t2 order by t2 limit 3) as abc;
or like this:
select sum(t1) from t2 where t2.t2 in(select t2 from t2 order by t2 limit 3) ;
ASKER
Nice try...
What species of SQL is that?
Mysql (unless there's been a recent development) doesn't do sub-selects which is a pity - in the past I've had to simulate sub-selects with php loops. It's just that summing the last x values seems like such an obvious thing to want to do. Might not be possible though.
RE: selecting into a temporary table (Curri)... Do you know how these stand up to reasonably high volumes of traffic? What actually happens if there are simultaneous requests?
What species of SQL is that?
Mysql (unless there's been a recent development) doesn't do sub-selects which is a pity - in the past I've had to simulate sub-selects with php loops. It's just that summing the last x values seems like such an obvious thing to want to do. Might not be possible though.
RE: selecting into a temporary table (Curri)... Do you know how these stand up to reasonably high volumes of traffic? What actually happens if there are simultaneous requests?
Hi
I don't really know what happens with temporary tables (I use postgresql mostly). If you're using transactions (which are available in some table types) you should not have any problem with simultaneous queries.
Temporaries *should* be faster than php loops in most cases, since it is the DB server that does the work, and there is no network lag.
You may need to do some cheating if you are not using transactions, like generating a new name for the temporary table each time.
Also, you may try to do 2 queries, first one that gets the 3 ids, and then one using an in, like my second option, but with a list of values (that you got from the first query). I think this is supported in mysql, and is probably faster than loops.
Of course, you may also try to use a real database :) (sorry, couldn't resist the temptation :)
Orlando
I don't really know what happens with temporary tables (I use postgresql mostly). If you're using transactions (which are available in some table types) you should not have any problem with simultaneous queries.
Temporaries *should* be faster than php loops in most cases, since it is the DB server that does the work, and there is no network lag.
You may need to do some cheating if you are not using transactions, like generating a new name for the temporary table each time.
Also, you may try to do 2 queries, first one that gets the 3 ids, and then one using an in, like my second option, but with a list of values (that you got from the first query). I think this is supported in mysql, and is probably faster than loops.
Of course, you may also try to use a real database :) (sorry, couldn't resist the temptation :)
Orlando
ASKER
I'll try using temp tables with unique generated names.
As far as I'm aware Mysql doesn't support Transactions, sub-selects, stored procedures and a couple of other things that you'd possibly expect it to have. It is very stable and very fast though.
Thanks for your help
Nick
As far as I'm aware Mysql doesn't support Transactions, sub-selects, stored procedures and a couple of other things that you'd possibly expect it to have. It is very stable and very fast though.
Thanks for your help
Nick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers for that - I wound up doing it with php loops
as in
http://www.the-afs.com/database/datapage.php3?divID=1&seasonend=2001&pagetype=Form&formtype=Last%204%20Games
(still under development (panic, moi?))
The php loop way is less elegent but quite flexible. Mysql is pretty good but the lack of subselects and stored procedures creates dependence on external scripting - which seems a little clumsy sometimes. Still, there you go.
as in
http://www.the-afs.com/database/datapage.php3?divID=1&seasonend=2001&pagetype=Form&formtype=Last%204%20Games
(still under development (panic, moi?))
The php loop way is less elegent but quite flexible. Mysql is pretty good but the lack of subselects and stored procedures creates dependence on external scripting - which seems a little clumsy sometimes. Still, there you go.
select team, sum(goals) from (
select * from games g1
where 3 >
(select count(*) from games g2
where g2.dt>g1.dt
and g1.team=g2.team)) m1
group by m1.team
3 represents the last '3' games, u could make it dynamic.
cheers
ajax