Solved

MySQL complex Sum query

Posted on 2001-08-03
9
658 Views
Last Modified: 2012-08-13
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
Comment
Question by:Nick1181
9 Comments
 

Expert Comment

by:ajax625
Comment Utility
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
 
LVL 2

Expert Comment

by:curri
Comment Utility
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
 

Author Comment

by:Nick1181
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:curri
Comment Utility
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
 

Author Comment

by:Nick1181
Comment Utility
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
 
LVL 3

Accepted Solution

by:
bobj_kirk earned 200 total points
Comment Utility
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
 

Author Comment

by:Nick1181
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now