CODEWITHSUNDEEP
mysql
kuriouscoder
kuriouscoder

Reputation: 5582

MYSQL Friday grouping for report

We need to run reports on Friday to Friday basis for the entire year. I do not see week() function handy for this.

Say we have a table Summary, having two columns - count and creation_date. I need to aggregate counts based on creation_date spanning from Friday to Friday. This needs to be run for a couple of years.

Any suggestions would be appreciated.

Upvotes: 1

Views: 77

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

Here is a query to tell you the previous Friday and the following Thursday from today

SELECT DATE(week_beg),DATE(week_end) FROM
(SELECT daywk_beg + INTERVAL 0 second week_beg,
daywk_beg + INTERVAL 604799 second week_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktoday DAY) daywk_beg
FROM (SELECT SUBSTR('2345601',wkndx,1) daysbacktoday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt)
AAAAA) AAAA) AAA) AA) A;

Here is today's results

mysql> SELECT DATE(week_beg),DATE(week_end) FROM
    -> (SELECT daywk_beg + INTERVAL 0 second week_beg,
    -> daywk_beg + INTERVAL 604799 second week_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktoday DAY) daywk_beg
    -> FROM (SELECT SUBSTR('2345601',wkndx,1) daysbacktoday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt)
    -> AAAAA) AAAA) AAA) AA) A;
+----------------+----------------+
| DATE(week_beg) | DATE(week_end) |
+----------------+----------------+
| 2013-03-22     | 2013-03-28     |
+----------------+----------------+
1 row in set (0.00 sec)

I wrote a query like this in the DBA StackExchange

Here is a sample table

CREATE TABLE summary
(
    id int not null auto_increment,
    ...
    `count` int not null default 0,
    creation_date date,
    primary key (id)
);

To get your query to tally results based on Fridays, you need this

SELECT SUM(`count`) count_sum,friday FROM
(SELECT `count`,DATE(week_beg) friday
(SELECT daywk_beg + INTERVAL 0 second week_beg,
daywk_beg + INTERVAL 604799 second week_end,`count`
FROM (SELECT `count`,(DATE(NOW()) - INTERVAL daysbacktoday DAY) daywk_beg
FROM (SELECT `count`,SUBSTR('2345601',wkndx,1) daysbacktoday
FROM (SELECT `count`,DAYOFWEEK(dt) wkndx FROM
(SELECT `count`,creation_date FROM dt)
AAAAA) AAAA) AAA) AA) A) fri
GROUP BY friday;

Give it a Try !!!

Upvotes: 1

What have you tried
What have you tried

Reputation: 11148

Select 
count, creation_date,
from
(
    SELECT 
    count, creation_date
    DATEDIFF(week, '2013-03-30', date) AS WeekNumber
    FROM Summary
)
GROUP BY
count,
creation_date,
WeekNumber

You would enter your start date in place of 2013-03-30

Upvotes: 0

fthiella
fthiella

Reputation: 49069

You could group by

YEARWEEK(`date` - INTERVAL 5 DAY)

or you can calculate your start_week_date and end_week_date with this:

SELECT
  `date`,
  `date` - INTERVAL (DAYOFWEEK(`date`) + 1) % 7 DAY start_week_date,
  `date` + INTERVAL 6 - (DAYOFWEEK(`date`) + 1) % 7 DAY end_week_date
FROM
  dates

Please see this fiddle.

So your query could be like this:

SELECT
  creation_date - INTERVAL (DAYOFWEEK(creation_date) + 1) % 7 DAY start_week_date,
  SUM(count)  --- or your aggregate function
FROM
  Summary
GROUP BY
  creation_date - INTERVAL (DAYOFWEEK(creation_date) + 1) % 7 DAY start_week_date

Upvotes: 0

Related Questions