How do i count number of records and group by date every 7 days SQL

Question

I am trying to write a query that counts the number of records that were created every 7 days, Using this query i get the number of records created per day,

SELECT 'Closed' AS `Status`, COUNT(*) AS `Count`, `raisedon` AS `Date` FROM table WHERE raisedon >= '2019-01-01' and raisedon < '2019-03-29' AND status = 'Open' AND type = 'A' AND location = 'B' AND locationid = 'C' GROUP BY raisedon

This returns

Closed 1 2019-01-01
Closed 1 2019-01-14
Closed 2 2019-01-16
Closed 1 2019-01-24
Closed 1 2019-01-25
Closed 1 2019-01-30
Closed 1 2019-02-01
Closed 1 2019-02-03
Closed 1 2019-02-28
Closed 1 2019-03-07
Closed 1 2019-03-08

I would like the results to be like

Closed 1 2019-01-01
Closed 1 2019-01-08
Closed 2 2019-01-15
Closed 2 2019-01-22
Closed 3 2019-01-29
Closed 0 2019-02-05
Closed 0 2019-02-12
Closed 0 2019-02-19
Closed 1 2019-02-26
Closed 2 2019-03-05

Is this possible with just a query or will i have to use javascript aswell, I am using phpmyadmin with mysql

Thanks for any advice

Tim Biegeleisen · Answer 1 · 2019-04-08T15:55:15.157

If you are not too picky about your edge conditions for where a given week starts, you might just be able to aggregate using YEARWEEK:

SELECT
    'Closed' AS Status,
    COUNT(*) AS cnt,
    YEARWEEK(raisedon) AS week
FROM yourTable
WHERE
    raisedon >= '2019-01-01' AND raisedon < '2019-03-29' AND
    status = 'Open' AND
    type = 'A' AND
    location = 'B' AND
    locationid = 'C'
GROUP BY
    YEARWEEK(raisedon);

This answer assumes that your data would have at least one data point present for each week. If there could be large gaps, then one solution would be to join with a calendar table. Here is an example of how to do this:

SELECT
    'Closed' AS Status,
    COUNT(t2.raisedon) AS cnt,
    YEARWEEK(t1.raisedon) AS week
FROM
(
    SELECT '2019-01-01' AS raisedon UNION ALL
    SELECT '2019-01-02' UNION ALL
    SELECT '2019-01-03' UNION ALL
    ...
    SELECT '2019-01-31' UNION ALL
    SELECT '2019-02-01' UNION ALL
    ...
    SELECT '2019-03-31'
) t1
LEFT JOIN yourTable t2
    ON t1.raisedon = t2.raisedon
WHERE
    t1.raisedon >= '2019-01-01' AND t1.raisedon < '2019-03-29' AND
    t2.status = 'Open' AND
    t2.type = 'A' AND
    t2.location = 'B' AND
    t2.locationid = 'C'
GROUP BY
    YEARWEEK(t1.raisedon);

See here for several ways to generate a calendar table like the one used above in the second query.

thanks for the answer, there are going to be gaps in the data, how would i use a calendar table, i am relatively new to this — Murderous Koala, Apr 08 '19 at 15:11
@MurderousKoala Check my updated answer for a calendar table approach. — Tim Biegeleisen, Apr 08 '19 at 15:56
I likeuse @tim's approach. Cleaned up original. I often prefer doing year and week separately. "Min" of the date field will reveal the earliest date in the data for that week. `SELECT YEAR(raisedon) AS Yr_Date , WEEK(raisedon) AS Wk_Date , Status , COUNT(*) AS Count , MIN(raisedon) AS min_Date FROM table WHERE raisedon >= '2019-01-01' AND raisedon < '2019-03-29' AND type = 'A' AND location = 'B' AND locationid = 'C' GROUP BY YEAR(raisedon) , WEEK(raisedon) , Status ORDER BY YEAR(raisedon) AS Yr_Date , WEEK(raisedon) AS Wk_Date , Status` — wij, Apr 08 '19 at 17:48

score 0 · Answer 2 · answered Apr 08 '19 at 14:36

0

One method uses datediff():

SELECT 'Closed' AS `Status`,
       COUNT(*) AS `Count`,
       MIN(raisedon) AS `Date`
FROM table
WHERE raisedon >= '2019-01-01' AND
      raisedon < '2019-03-29' AND
      status = 'Open' AND
      type = 'A' AND
      location = 'B' AND
      locationid = 'C'
GROUP BY FLOOR(DATEDIFF('2019-01-01', raisedon) / 7);

answered Apr 08 '19 at 14:36

Gordon Linoff

1,242,037
58
646
786

Thanks, but this returns ``` Closed 2 2019-03-07 Closed 1 2019-02-28 Closed 3 2019-01-30 Closed 2 2019-01-24 Closed 2 2019-01-16 Closed 1 2019-01-14 Closed 1 2019-01-01 ``` i want to be able to have a 0 count if no records are found so that the there are no gaps in weeks for example 2019-01-01 jumps straight to 2019-01-14, it skips a date – Murderous Koala Apr 08 '19 at 15:09
@MurderousKoala . . . In that case, you need some sort of calendar table. – Gordon Linoff Apr 08 '19 at 16:55

How do i count number of records and group by date every 7 days SQL

2 Answers2