Adding score for a range of dates

Hi
I am working on a table where user’s score is stored along with the date and their userid.

Now I want to create a page where they would be able to see their weekly score as one number .

For example a user has 10, 20 , 5 , 35 score on mon, wed, fri, sat .
so on their scorecard they would see their weekly score as week 01 = 70, week 02 = 65 etc etc

current query I have in my mind

select scores.userid, sum(score) as sum 
from scores 
where scores.userid = '2222' 
and scores.created between '2015-07-01 00:00:00' and DATE_ADD('2015-07-01 00:00:00', INTERVAL 7 DAY )

In this scenario I have to run query multiple times to get scores for each week.

Is there a better way to get result from query like

     2222 | 20
     2222 | 25
     2222 | 33 

and so on for each week?

Thanks

your query is fine except it’s missing the GROUP BY clause

you can run one query for multiple weeks, just put WEEK() or WEEKOFYEAR() – whichever week function you prefer – into both SELECT and GROUP BY clauses

1 Like

Thanks for reply

For reference my test data is

"id","score","userid","created"
"1","20","2","2015-06-01 10:40:51"
"2","10","2","2015-06-02 10:41:49"
"3","15","2","2015-06-03 10:42:15"
"4","22","2","2015-06-04 10:42:36"
"5","23","2","2015-06-05 10:42:50"
"6","32","2","2015-06-06 10:43:03"
"7","21","2","2015-06-07 10:43:16"
"8","56","2","2015-06-08 10:43:32"
"9","9","2","2015-06-09 10:43:56"
"10","11","2","2015-06-10 10:44:08"
"11","17","2","2015-06-11 10:44:21"
"12","25","2","2015-06-12 10:44:34"
"13","87","2","2015-06-13 10:44:48"
"14","34","2","2015-06-14 10:45:03"
"15","13","2","2015-06-15 10:45:16"
"16","44","2","2015-06-16 10:45:27"
"17","28","2","2015-06-17 10:45:47"
"18","12","2","2015-06-18 10:45:58"
"19","72","2","2015-06-19 10:46:12"
"20","31","2","2015-06-20 10:46:22"
"21","16","2","2015-06-21 10:46:50"
"22","19","2","2015-06-22 10:47:01"
"23","37","2","2015-06-23 10:47:12"
"24","35","2","2015-06-24 10:47:26"  

Now when I run the following query

select scores.userid, sum(score) as sum, weekofyear('2015-06-01') as wk 
from scores 
where scores.userid = '2' 
and scores.created between '2015-06-01 00:00:00' and DATE_ADD('2015-06-01 00:00:00', INTERVAL 12 week )  
group by wk

I get following result

 userid | sum | wk
 2      | 689 | 23

I think I am doing something wrong here but have no idea.
Would you please help to fix the query.

Thanks

your GROUP BY clause is missing a column

right now, it wants to produce a total for each week – except you’re only asking for the single week of ‘2015-06-01’ as specified in the SELECT clause

i believe you wanted a total for each user per each of 12 weeks

1 Like

Thanks for reply and steering me in right direction

 select  scores.userid, sum(score) as sum, week(created) as wk
 from scores 
 where scores.userid = '2' 
 group by wk

I got following result

"userid","sum","wk"
"2",     "122","22"
"2",     "226","23"
"2",     "234","24"
"2",     "107","25"

Which is exactly what I was looking for and from comparing the result with previous query I think following condition is not required

  and scores.created between '2015-06-01 00:00:00' and DATE_ADD('2015-06-01 00:00:00', INTERVAL 12 week )   

Am I right?

Thanks.

i thought you wanted to restrict the range of weeks?

also, if you’re going to run this for only a single user at a time, you don’t need that user in the SELECT clause

SELECT WEEK(created) AS wk , SUM(score) AS sum FROM scores WHERE userid = 2 AND created BETWEEN '2015-07-01' AND '2015-07-01' + INTERVAL 7 DAY GROUP BY wk

Yeah, I wanted to restrict the weeks but after going through the query and realizing that it doesn’t matter as much as I thought it would.

Thanks for improving the query.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.