mysql - Query to get total orders placed by some specific customer in last year retrieves repeated dates when no order exists

Question

I am working on a query to get total orders placed in last year by some specific customer (id = 329) using two tables viz. orders and calendar (this is to get zero fill values when no corresponding record exists) tables.

orders table:

calendar table:

query:

SELECT c.datefield AS date
     , IFNULL((SELECT COUNT(o.order_date) FROM orders 
WHERE o.customer_id = 329 LIMIT 1), 0) AS TotalOrders 
  FROM calendar AS c 
  LEFT 
  JOIN orders AS o 
    ON c.datefield = DATE(o.order_date) 
WHERE YEAR(c.datefield) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR) 
GROUP
    BY date
     , o.customer_id 
 ORDER 
    BY date ASC 

output:

From above picture, you can see that 2 orders were placed by customer 329 on 2020-01-02. But two extra rows for the same date with 0 order are retrieved. I guess it might be probably from customer 6882, and 670. This is wrong! I only need to fetch orders of customer 329.

How can I exclude these unwanted rows in my query and retrieve total orders by only customer 329?

Answer 1

To count total orders placed monthly in last year by a particular customer, use this query:

SELECT MONTHNAME(c.datefield) AS Month, 
    YEAR(CURRENT_DATE - INTERVAL 1 YEAR) AS Year, 
    IFNULL(o.TotalOrders, 0) AS Orders 
    FROM calendar AS c 
    LEFT JOIN (
        SELECT MONTH(o.order_date) AS Month, 
        YEAR(o.order_date) AS Year, 
        COUNT(o.customer_id) AS TotalOrders 
        FROM orders AS o 
        WHERE YEAR(o.order_date) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR) AND o.customer_id = 329 
        GROUP BY Month) AS o 
    ON MONTH(c.datefield) = o.Month 
    GROUP BY MONTH(c.datefield)