[thelist] MySQL selecting sum of records from time period

[Matt Warden]

On Tue, 17 Aug 2004 15:20:30 -0700, David Siedband
<technique at oceanicsky.com> wrote:
> 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)

You probably shouldn't be using the autoincrement field as a time
reference. You might want to think about using a date field.

That said:

select sum(cCost) as cCost, ... sum(cTotal) as cTotal
from dailyTotalsA
having ID <= max(ID) and ID >= (max(ID)-7);

and, really, there is no reason to say <= max(ID) because that has to
be true by definition of max(). So:

select sum(cCost) as cCost, ... sum(cTotal) as cTotal
from dailyTotalsA
having ID >= (max(ID)-7);


-- 
mattwarden
mattwarden.com


This email proudly and graciously contributes to entropy.