Weekly grouping in MySQL

Question

Table structure

Table name: btq_user name, email, kall, state_id, datain 

Now I want to count numerical entries that have kall = 1 or state_id in (51, 20, 46) weekly. I need results only weekly (Mon - Sun), regardless of whether there will be changes in the year. let's say, for example, 12/31/2012 - Monday and 6-1-2013 - Sunday, so the result should include this as a week. regardless of whether there will be changes in the year, he should only rely on weeks.

This is what I tried but did not work.

 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 have the opportunity to show the weeks (31-12-2012 - 6-1-2013) with the results?

Thanks for reading my question.

Answer 1

Two steps to this:

First, you will need a week truncation operation — this will take your DATETIME element and return at midnight on the previous Sunday (if your business rule says that the week starts on Sunday).

This becomes a suitable GROUP BY element. Neke () / YEAR () hacking is not suitable for this. It really makes a mess in the last / first week of each year.

In my experience, this expression will do the week truncation trick for you, Sunday is Saturday,

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

To get Monday through Sunday use this expression.

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

So you can do it.

  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 this truncated date so that you can display the last day of each week along with the first day.

This is a good way to do this, with a subquery

 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 

Do a lot of this? I suggest you create a saved function TRUNC_WEEK .