CODEWITHSUNDEEP
mysql
faza
faza

Reputation: 257

how to group by week start from friday

I have table sql like this:

column id and tanggal is primary key

This my query for count tgl:

SELECT count( tgl ) AS total, absen.id
FROM absen
WHERE absen.status = 'm'
GROUP BY absen.id

So I want group by absen.id and absen.tgl How to group by week from Friday to Thursday? 2016-01-08 is friday and 2016-01-15 is thursday.

Upvotes: 2

Views: 2805

Answers (2)

Michael
Michael

Reputation: 11

I just found how to get this by trouble shooting on excel by using this WEEK('date' + INTERVAL 3 DAY, 3)

Upvotes: 0

Mobasher Fasihy
Mobasher Fasihy

Reputation: 1061

Bellow query can bring the result you want, but i think you defined the wrong end date, because in your example from 2015-01-08 up to 2015-01-15 its 8 day and one week has 7 days.

select
   count( tgl ) AS total, 
   absen.id,
   CASE WHEN (weekday(tgl)<=3) THEN date(tgl + INTERVAL (3-weekday(tgl)) DAY)
    ELSE date(tgl + INTERVAL (3+7-weekday(tgl)) DAY)
   END as week_days
FROM absen
WHERE status = 'm'
GROUP BY id,week_days

here is the fiddle fiddle

Query Description: mysql weekday array numbers:

$weekArr = array(
        'Monday' => 0,
        'Tuesday' => 1,
        'Wednesday' => 2,
        'Thursday' => 3,
        'Friday' => 4,
        'Saturday' => 5,
        'Sunday' => 6);

So now suppose today is Tuesday and date is 2016-01-12, now let's count from today towards the start date in our table which is 2016-01-07 and it match with Thursday of past week, so according to the weekday array number its weekday(2016-01-07) == 3 so it goes to the WHEN part of our query, and query will select something like this CASE WHEN (weekday('2016-01-07') <= 3) THEN date('2016-01-07' + INTERVAL(3-3)) that is equal to SELECT '2016-01-07' and so on for others.

Upvotes: 2

Related Questions