Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

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.



0
Nick1181
Asked:
Nick1181
1 Solution
 
ajax625Commented:
try this query :

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
0
 
curriCommented:
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) ;

0
 
Nick1181Author Commented:
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?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
curriCommented:
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
0
 
Nick1181Author Commented:
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
0
 
bobj_kirkCommented:
Nick,

I am not a MySQL programmer, but I thought I would try to solve your problem without sub-selects. I wrote code that works on MSSQL using 3 ANSI joins. The code uses character dates for simplicity, but a true date or datetime field could be used as long as the select converts it to a fixed length YMD character string. The code assumes only one game on any day. Use a datetime field appropriately converted if multiple games are allowed on one day. The code uses the MSSQL functions "substring" and "convert(char)", but I am sure the equivalent functions must be available in MySQL.

Here is my complete test setup:
create table GameResults (
team varchar(10), goals int, [date] char(8))

insert into GameResults (team, goals, [date]) values ('White', 5, '20010101')
insert into GameResults (team, goals, [date]) values ('White', 4, '20010102')
insert into GameResults (team, goals, [date]) values ('White', 3, '20010103')
insert into GameResults (team, goals, [date]) values ('White', 2, '20010104')
insert into GameResults (team, goals, [date]) values ('White', 1, '20010105')
insert into GameResults (team, goals, [date]) values ('Black', 10, '20010101')
insert into GameResults (team, goals, [date]) values ('Black', 20, '20010102')
insert into GameResults (team, goals, [date]) values ('Black', 30, '20010103')
insert into GameResults (team, goals, [date]) values ('Black', 40, '20010104')

Here is my query:
select g1.team, substring(max(g1.[date]+g2.[date]+g3.[date] + ' ' +
                convert(char, g1.goals+g2.goals+g3.goals)),25,10) as goals
from GameResults g1 join GameResults g2
  on g1.team=g2.team
 and g1.[date]<>g2.[date]
join GameResults g3
  on g1.team=g3.team
 and g1.[date]<>g3.[date]
 and g2.[date]<>g3.[date]
group by g1.team


Here are my results:
team       goals      
---------- ----------
Black       90      
White       6        

Hope this helps.

-Bob
0
 
Nick1181Author Commented:
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now