Group by week in MySQL

Question

Table structure is,

Table name: btq_user

name, email, kall, state_id, datain

Now i want to count number records which has kall = 1 or state_id in( 51, 20, 46) by weekly. i need the results only by weekly (Mon - Sun) no matter if year changes. lets say for example 31-12-2012 is Monday and 6-1-2013 is Sunday so result should include that as a week. no matter if year changes it should count only with weeks.

This is what i tried but not working.

SELECT 
count( if( A.state_id = 51 or A.state_id = 20 or A.state_id = 46, A.state_id,
null  ) ) AS state_total, count( if( A.kall =1, A.kall, null ) ) AS appointment, 
CONCAT( WEEK( A.datain) , " - 
", YEAR( A.datain ) ) AS week
FROM btq_user A
GROUP BY week
ORDER BY A.datain ASC

also is there any possibility to display weeks ( 31-12-2012 - 6-1-2013 ) with results ?

Thank you for reading my question.

Take a look at this question and answer. http://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql — O. Jones, Aug 17 '13 at 11:56
Do you have a index on column datain? And how many records are in the table right now? — Raymond Nijland, Aug 17 '13 at 12:29

score 3 · Accepted Answer · edited May 23 '17 at 12:05

Two steps to this:

One, you need a week-truncate operation -- that will take your DATETIME item and give back midnight on the preceding Sunday (if your business rule says that the week begins on Sunday).

That becomes a suitable GROUP BY item. The WEEK() / YEAR() hack isn't suitable for this. It really makes a mess in the last/first week of each year.

In my experience, this exxpression will do the week-truncate trick for you, Sunday - Saturday,

  FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))

To get Monday - Sunday weeks, use this expression.

  FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -2, 7))

So you can do this.

  SELECT COUNT(whatever), SUM(whatelse),
         FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7)) as WEEKSTART,
    FROM TABLE
   GROUP BY FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7))

How to group by week in MySQL?

Second, you need to add six days to that truncated date, so you can display the last day of each week along with the first day.

This is a good way to do that, with a nested query

SELECT whats, elses, weekstart, weekstart + INTERVAL 6 DAY AS weekend
  FROM (
  SELECT COUNT(whatever) AS whats, SUM(whatelse) AS elses,
         FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7)) AS weekstart,
    FROM TABLE
   GROUP BY FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7))
  ) AS summary
ORDER BY weekstart

Doing lots of this? I suggest you create a stored TRUNC_WEEK function.

can you give me an example ? i need week totals even for 31-12-2012 To 6-1-2013 (Mon To Sun). thanks — hardik, Aug 17 '13 at 12:05

Group by week in MySQL

1 Answers1

Linked