CODEWITHSUNDEEP
phpmysql
Sami Al-Subhi
Sami Al-Subhi

Reputation: 4662

Count records grouped by week of time

let's assume I have this numrequest table. it has these fields: id, id_user, time(timestamp)

I want to count the records for a specific $id_user grouped by week.
I know how to count the records for a period of time.

  $past=time()-$totalSecondsInAweek

  $query='SELECT COUNT(1) as counta
          from numrequest
          WHERE time >'.$past.'
          AND WHERE id_user='.$id_user;

but how to do that so it counts per each week since the first input regarding a specific $id_user

Upvotes: 2

Views: 2508

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

My main doubt, and possible unrelated to the question itself, is why are you doing this?

$past=time()-$totalSecondsInAweek WHERE time >'.$past.'

Are you trying to get records only from the last 7 days?

If that is the case, please, try this in your sql code:

WHERE time > date_sub(curdate(), INTERVAL 7 DAY)

In my solution I'll be removing this condition because I don't understand it and I just can't link it to grouping by week.

Now, to your answer. My bet is that you don't want just to group by week. You want to group by the pair (Year-Week). If you don't do this, you'll group in the week number 0 both dates: "2011-01-01" and "2012-01-01". So, I assume you would like to have the previous data displayed this way:

Year | Week | Count(*)
----------------------
2011 | 0    | 1
2012 | 0    | 1

Rather than:

Week | Count(*)
---------------
0    | 2

So, here is the SQL code:

select
    id_user,
    year(time) as AYear, week(time) as AWeek,
    count(*) as TotalPerWeek
from n1
where id_user = 'tim'
group by id_user, AYear, AWeek
order by AYear, AWeek

Here is an example

If you would like to have the year and week in only one column, you can try this (which I think will be faster than a CONCAT(year, week)):

select
    id_user,
    year(time) * 100 + week(time) as YearWeek,
    count(*) as TotalPerWeek
from n1
where id_user = 'tim'
group by id_user, YearWeek
order by YearWeek

Here is an example

Note: If you want your week numbers to start in 0 or to choose whether it should start on monday or sunday check the official documentation.

Hope this helps.

Upvotes: 2

Fahim Parkar
Fahim Parkar

Reputation: 31635

$past=time()-$totalSecondsInAweek

$query='SELECT COUNT(1) as counta
      from numrequest
      WHERE time >'.$past.' AND id_user='.$id_user 
      GROUP BY WEEKOFYEAR(time);

This will get what you want...

Upvotes: 0

Jovan Perovic
Jovan Perovic

Reputation: 20193

You would have to use GROUP BY with 2 predicates, that is, fitst by id_user and second by WEEK() function for time field:

SELECT id_user, COUNT(*) FROM numrequests WHERE time > some_date GROUP BY id_user, WEEK(time);

This is true, if you're not seeking results for single user, if which case you need to set WHERE predicate

Upvotes: 0

dani herrera
dani herrera

Reputation: 51655

YOu can use weekofyear function. Your query:

  $query='SELECT id_user, weekofyear(time) as `week`, COUNT(1) as counta
          from numrequest
          WHERE time >'.$past.'
          AND WHERE id_user='.$id_user.'
          GROUP BY id_user, weekofyear(time)';

You can query for several users at a time:

  $id_user = '1,2,3,4,5';
  $query='SELECT id_user, weekofyear(time) as `week`, COUNT(1) as counta
          from numrequest
          WHERE time >'.$past.'
          AND WHERE id_user in ('.$id_user.')
          GROUP BY id_user, weekofyear(time)';

Upvotes: 0

Related Questions