[RESOLVED] SUM number of hits from the last week ?

MichaelHe

I'm having bit of a problem with retrieving the right data from my mysql database.

My query looks simply like this:

SELECT *, SUM(cc.Counter) as Count
FROM Costumer co LEFT JOIN Clickcounter cc ON (cc.CostID=co.CostumerID)
GROUP BY co.CostumerID
ORDER BY Count DESC
");

This works fine and lists the costumers with the costumer with the most visits first and so on.

But...
The Clickcounter table look like this showing how many thime their porfil has been shown each day.

CostID | Date and | Counter.

1 | 2008-08-07 | 4
1 | 2008-08-08 | 5
1 | 2008-08-09 | 2
1 | 2008-08-10 | 8

2 | 2008-08-07 | 7
2 | 2008-08-08 | 3
2 | 2008-08-09 | 1
2 | 2008-08-10 | 9

3 | 2008-08-07 | 8
3 | 2008-08-08 | 7
3 | 2008-08-09 | 2
3 | 2008-08-10 | 5

and so on...

So here comes the problem.
Instead of getting the sum of all counts by one costumer I need to only get the sum of the visits the last week.
So that the costumer who has the most visits a week back from now is listed first.

I guess that it's the "SUM(cc.Counter) as Count" I have to change, but how ?
Something about :
SUM(cc.Counter WHERE .. something about a week back from now... ) as Count
or maybe
SUM(cc.Counter WHERE (Select * from Clickcount where .. Date>= ???? )) as Count

I'm a bit lost here.
Can someone figure this out ?

best
Michael

mwasif

Use SUM() to get counter and put the following in the where condition

WEEK(DATE_SUB(date_col, INTERVAL 1 WEEK)) = WEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) 
AND 
YEAR(DATE_SUB(date_col, INTERVAL 1 WEEK)) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))

MichaelHe

First.. thanks for your answer.. :o)

2 things comes to my mind.

The first one.
I'm not very good at this date thing but doesn't that sum the hits from the same week as the current week.
Eks if it's a thursday, it sums all hits from monday till thursday and not from last thursday til this thursday ?

The second one.
When you put it in your WHERE condition, what if a costumers hasn't had any hits at all in the last week. Then I will guess that this costumer will not be shown at all where it should be shown first with 0 hits ?
Or am I wrong ?

MichaelHe

Off cause... I Got it 🙂

Instead of

SELECT *, SUM(cc.Counter) as Count 
FROM Costumer co LEFT JOIN Clickcounter cc ON (cc.CostID=co.CostumerID) 
GROUP BY co.CostumerID 
ORDER BY Count DESC 
");

I only LEFT JOIN the part of the Clickcounter table I want like this

$date= date("Y-m-d", mktime(0,0,0,date("m"),date("d")-7,date("Y")));
SELECT *, SUM(cc.Counter) as Count 
FROM Costumer co LEFT JOIN (SELECT * FROM Clickcounter WHERE Date>='$date' ) cc ON (cc.CostID=co.CostumerID) 
GROUP BY co.CostumerID 
ORDER BY Count DESC 
");

Why didn't I think of that 🙂