MySQL sum of last seven days where data was collected

3dron

I am having a hell of a time figuring this one out.

I have a database that I enter dollar amounts on certain days and not others. And sometimes multiple dollar amounts are recorded for the same day.

Database looks something like this:

SDate | Time | Amount
08-02-28 | 14:00:00 | $55
08-03-01 | 09:00:00 | $30
08-03-02 | 11:00:00 | $75
08-03-02 | 20:00:00 | $20
08-03-05 | 15:00:00 | $50
08-03-06 | 18:15:00 | $40
08-03-06 | 12:00:00 | $15
08-03-08 | 15:30:00 | $65
08-03-09 | 10:30:00 | $10
08-03-09 | 16:00:00 | $20
08-03-12 | 19:00:00 | $45
08-03-15 | 11:30:00 | $20
08-03-16 | 21:30:00 | $25

Currently I query the last 7 days sum with something like:
SELECT SUM(amount) as net7d FROM data WHERE DATE(SDATE) > DATE(curdate() -7);

But what I really want is to check the last 7 days of activity and total those. Is there an easy way to do this in one query?

Thanks for answer or any guidance/ideas in this direction.:eek:

Ron R. Jr.

NogDog

How about...

SELECT SUM(amount) AS net7d FROM data WHERE SDate IN 
(SELECT DISTINCT SDate FROM data ORDER BY SDate DESC LIMIT 7)

richie

SELECT SUM(amount) as net7d FROM data WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= SDATE;

3dron

Limit & is on the right track but since there are sometimes multiple records on a same date, a limit only gets me last 7 records. Not last 7 days.

And interval 7 days seems to only get me last seven days in a row. Just like my "WHERE DATE(SDATE) > DATE(curdate() -7);"

Does my exact dilema make sense?

bogu

Something like this:

SELECT
	SUM(`amount`), t.`date`
FROM `table` t
GROUP BY t.`date`
ORDER BY t.`date` DESC
LIMIT 7

NogDog

3dron wrote:
Limit & is on the right track but since there are sometimes multiple records on a same date, a limit only gets me last 7 records. Not last 7 days.
...

Which is precisely why I used "DISTINCT" in my subquery.

3dron

NogDog, sorry I missed your answer. THough it was the first I was only emailed on the 2nd two reponses. So I never even noticed your was there.

Well my browser reports that mySQL version does not support 'LIMIT & IN/ALL/ANY/SOME subquery'

Looks like you code will work. It grabs the last 7 dates recorded, then grabs the sum of all date 'IN' that sub-set, correct?

So I tried this 'patch'. It seems to be correct.

SELECT SUM(amount) AS net7d FROM data WHERE SDate >=
(SELECT DISTINCT SDate FROM data ORDER BY SDate DESC LIMIT 6,0)

Will I have any problems with this?

Thanks for your quick help.

NogDog

Shouldn't that be . . . LIMIT 6,1)?

3dron

opps, typo. I did use 6,1 in my code.