CODEWITHSUNDEEP
mysqlsqldatetimestampleft-join
YellowMinion
YellowMinion

Reputation: 242

How to fix mySQL query to find all dates of month mapped with total Count for each date including zeros?

I have an orders table with a Date column of timestamp. I want to count number of orders placed for each date including zeros for dates when No order was placed, like this:

TotalOrders--------- Date

12------------------ 01/02/2019

0------------------- 02/02/2019

7------------------- 03/02/2019

0------------------- 04/02/2019

4------------------- 05/02/2019

0------------------- 06/02/2019

I searched this here and got one solution:

select COUNT(o.OrderID) AS TotalOrders, a.date

from ( select date_add('2019-07-01', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date

from (select 0 as num union all select 1 union all select 2 union

all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) n1,

(select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) n2,

(select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) n3,

(select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9) n4,

(select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) n5

) a

LEFT JOIN orders o ON MONTH(o.Date)= MONTH(a.date)

AND YEAR(o.Date) = YEAR(a.date) where MONTH(a.date) ='7' and

YEAR(a.date) = '2019' GROUP BY a.date order by a.date

EDIT: As in July 2019 total 4 orders were placed so when I used above solution, it gives me 4 result for each dates. instead It should have showed order Count for respective date and zero for dates with no order. But it shows 4 for each date. Please let me know where I'm doing it wrong. my Orders table is:

orders(OrderID(p.k), Date, totalPrice, CustomerID)

Upvotes: 0

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your sample data has no examples from July 2019.

I would also suggest that you use ranges for the where comparison, so for January 2019:

where a.date >= '2019-01-01' and a.date < '2019-02-01'

Upvotes: 0

Related Questions