MySQL SQL Query

Question

I am trying to create a query which will check how many entries I have in my database for the last 7 days, it works, but it only returns the dates where records exist. I would like it to return a 0 for days where there is no data as I am producing a 7 day report.

Any help gratefully received!

SELECT DATE(created_date) AS date, 
    (SELECT COUNT(id) 
    FROM records 
    WHERE DATE(created_date) = date AND method = 'tw') AS total_records 
FROM records 
WHERE created_date > DATE_SUB(NOW(), INTERVAL 7 day) 
GROUP BY date

sqlfiddle.com/#!2/fd03b/1 good resourse :)

user1516873
– user1516873

2012-10-08 13:23:33 +00:00
Commented Oct 8, 2012 at 13:23 — user1516873
– user1516873, Commented Oct 8, 2012 at 13:23

ypercubeᵀᴹ · Accepted Answer · 2012-10-08 13:16:57Z

2

SELECT DATE(NOW() + INTERVAL num.i DAY) AS date, 
       COUNT(r.id) AS total_records 
FROM 
        ( SELECT 0 AS i UNION ALL               -- where 0 means today
          SELECT -1 UNION ALL                   -- and -1 is yesterday
          ...
          SELECT -6
        ) AS num
    LEFT JOIN
        records AS r
            ON  r.created_date >= DATE(NOW() + INTERVAL num.i DAY)
            AND r.created_date < DATE(NOW() + INTERVAL 1+num.i DAY)
            AND r.method = 'tw'
GROUP BY num.i ;

An index on (method, created_date) will help efficiency.

answered Oct 8, 2012 at 13:16

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2012-10-08 13:02:16Z

1

SELECT case when created_date is null
            then 0
            else DATE(created_date)
       end AS date,
      (SELECT COUNT(id)
       FROM records
       WHERE DATE(created_date) = date 
       AND method = 'tw') AS total_records 
FROM records
WHERE created_date > DATE_SUB(NOW(), INTERVAL 7 day)
OR created_date is null
GROUP BY date

answered Oct 8, 2012 at 13:02

juergen d

205k40 gold badges303 silver badges377 bronze badges

3 Comments

Jon Over a year ago

Thank you very much, this query works but it seems very slow (probably my own poor initial attempt is to blame!) it's taking ~10 seconds on a database with 50k records

ChrisInEdmonton Over a year ago

Do you have an index on your created_date? You'll want one for this query.

ypercubeᵀᴹ Over a year ago

The DATE(created_date) will probably make the index useless (for that part).

Gordon Linoff · Accepted Answer · 2012-10-08 13:38:37Z

1

I suspect moving the subquery from the SELECT clause to the FROM clause will improve performance. However, you don't need a subquery at all, if you just want to count the tw records:

SELECT date(created_date) as date, 
       sum(case when method = 'tw' then 1 else 0 end) as total_records
FROM records 
WHERE created_date > DATE_SUB(NOW(), INTERVAL 7 day)
group by date(created_date)

This will return every day, assuming there is at least one type of record on each day. By the way, if you have a problem with created_date being NULL, then you can include the conditions suggested by Juergen.

edited Oct 8, 2012 at 13:38

user330315

answered Oct 8, 2012 at 13:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

user1516873 · Accepted Answer · 2012-10-08 14:07:46Z

1

Perfomance will depends of how many records you will have in 7-day interval.

select date, 
       sum(cnt) 
from (SELECT 
       count(id) as cnt, 
       date(created_date) as date
       FROM records 
      WHERE created_date > DATE_SUB(NOW(), INTERVAL 7 day)
            and method ='tw' 
            and created_date < now()
      group by date
      union all (select 0 as cnt, date(DATE_SUB(NOW(), INTERVAL 6 day)) as date)
      union all (select 0 as cnt, date(DATE_SUB(NOW(), INTERVAL 5 day)) as date)
      union all (select 0 as cnt, date(DATE_SUB(NOW(), INTERVAL 4 day)) as date)
      union all (select 0 as cnt, date(DATE_SUB(NOW(), INTERVAL 3 day)) as date)
      union all (select 0 as cnt, date(DATE_SUB(NOW(), INTERVAL 2 day)) as date)
      union all (select 0 as cnt, date(DATE_SUB(NOW(), INTERVAL 1 day)) as date)
      union all (select 0 as cnt, date(NOW()) as date)
     ) t

group by date

answered Oct 8, 2012 at 14:07

user1516873

5,2232 gold badges39 silver badges56 bronze badges

2 Comments

Jon Over a year ago

Hi, this approach worked on MyISAM but not for InnoDB - is it due to the select 0? each day now returns 0 on the new sql engine. Thanks

user1516873 Over a year ago

@Jon select 0 uses to add rows with 0 sum, if table hasn't record for that day. I don't know how mysql engine can affect that query.

Collectives™ on Stack Overflow

MySQL SQL Query

4 Answers 4

Comments

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related