'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