MYSQL SELECT QUERY show all dates between, even if data not present with count=0

Question

Below is the query i have written to fetch the order count with status=1 for last week.

  SELECT count(*) as order_count,
         DATE_FORMAT(order_date,'%d-%b-%Y') as order_date,
         status 
    FROM customer_order 
   WHERE date(order_date) >= curdate() 
                              - INTERVAL DAYOFWEEK(curdate())+6 DAY
     AND date(order_date) < curdate() 
                              - INTERVAL DAYOFWEEK(curdate())-1 DAY 
     AND status=1
GROUP BY DATE_FORMAT(order_date, '%Y%m%d'),
         status

I am getting result only for the dates present in the table. I need all the dates with count = 0 if data is not present for particular date.

Check this http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 — Abhik Chakraborty, Apr 24 '15 at 10:27

Oscar Pérez · Answer 1 · 2015-04-24T10:36:55.600

0

You could try:

SELECT DATE_FORMAT(c1.order_date,'%d-%b-%Y') AS order_date
  FROM customer_order AS c1
 WHERE DATE_FORMAT(c1.order_date,'%d-%b-%Y') 
                  IN ( SELECT DATE_FORMAT(c2.order_date,'%d-%b-%Y') as order_date2
                         FROM customer_order AS c2
                        WHERE date(DATE_FORMAT(c2.order_date,'%d-%b-%Y')) >= curdate() 
                                - INTERVAL DAYOFWEEK(curdate())+6 DAY
                          AND date(DATE_FORMAT(c2.order_date,'%d-%b-%Y')) < curdate() 
                                - INTERVAL DAYOFWEEK(curdate())-1 DAY 
                          AND c2.status=1
                     GROUP BY DATE_FORMAT(DATE_FORMAT(c2.order_date,'%d-%b-%Y'), '%Y%m%d'),
                              c2.status
                       HAVING count(*)=0
                     )

edited Apr 24 '15 at 10:36

answered Apr 24 '15 at 10:33

Oscar Pérez

4,377
1
17
36

getting unknown column order_date2 – user3044552 Apr 24 '15 at 10:36
What are you trying to find? – Oscar Pérez Apr 24 '15 at 10:44
i want the records with count for date with status =1 and if data is not present for particular date the count should be 0 and date should be present – user3044552 Apr 24 '15 at 10:48
But do these dates exist in `customer_order` ? – Oscar Pérez Apr 24 '15 at 11:09
Please, give us some example data and the expected result. – Oscar Pérez Apr 26 '15 at 18:49

MYSQL SELECT QUERY show all dates between, even if data not present with count=0

1 Answers1