'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
.
.
.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
