[thelist] MySQL selecting sum of records from time period

David Siedband technique at oceanicsky.com
Tue Aug 17 17:20:30 CDT 2004


I'm working on an query in mysql to select some totals from the 
previous week, previous month, etc.

I've got an auto-incrementing ID field, so that saves a bit of date 
manipulation work...

I was thinking this would do it

select sum(cCost) as cCost, sum(zCost) as zCost, sum(zTotal) as zTotal, 
sum(cTotal) as cTotal
from dailyTotalsA
where ID <= max(ID)
and ID >= (max(ID)-7)

but I get

Error, _mysql_exceptions.ProgrammingError: (1111, 'Invalid use of group 
function')

though it works fine when I replace the max() calculations with hard 
coded IDs like this...

select sum(cCost) as cCost, sum(zCost) as zCost, sum(zTotal) as zTotal, 
sum(cTotal) as cTotal
from dailyTotalsA
where ID >= 7
and ID <=14

suggestions?

TIA
--
David








More information about the thelist mailing list