CODEWITHSUNDEEP
group-bycountmariadbleft-join
Ron Ancheta
Ron Ancheta

Reputation: 45

How to Outer Join a Calendar table to view dates with 0 records

I have a table with records of orders by customers and a table with dates from Jan 2022 to 10 years. I wanted to get all numbers of customers made everyday for the last 28 days, including those with 0 customers recorded. So I needed to outer join the calendar table to the customer records. However, I cant use outer join correctly.

Here's how I done it:

SELECT order_date as 'date', COUNT(orderstatus) as 'customers'
FROM orders
RIGHT OUTER JOIN calendar ON
calendar.date = orders.order_date
WHERE sellerid = 11

Im getting:

date          customers
2022-01-02    9

I wanted to see:

date          customers
2022-01-01    0
2022-01-02    9
2022-01-03    0
.
.
.

Upvotes: 0

Views: 172

Answers (1)

forpas
forpas

Reputation: 164064

You would not get the results that you posted in your question unless you group by date, so I guess you missed that part of your code.

You need a WHERE clause to filter the calendar's rows for the last 28 days and you must move the condition sellerid = 11 to the ON clause:

SELECT c.order_date, 
       COUNT(o.order_date) customers
FROM calendar c LEFT JOIN orders o
ON o.sellerid = 11 AND o.order_date = c.date 
WHERE c.date BETWEEN CURRENT_DATE - INTERVAL 28 DAY AND CURRENT_DATE
GROUP BY c.order_date;

Upvotes: 1

Related Questions