Returning no. of orders and average spending for each customer in a given month using mysql

Question

I have tables on my database with schema:

customers (customerID: integer, fName: string, lName: string)
items (itemID: integer, description: string, price: integer)
orders (orderID: integer, itemID: integer, aID: integer, customerID: integer, date: date)

For each customer, I would like to return the customerID, the number of orders and the average amount of money the customer spent during March 2013 (Total spending for each customer/order in March) this includes ALL customers regardless of whether they actually bought something in March or not.

I have tried using join and other functions but have not been successful. Any help would be appreciated. Thanks!

Answer 1

SELECT
c.customerID,
COUNT(DISTINCT o.orderID) AS number_of_orders,
SUM(i.price) / COUNT(DISTINCT o.orderID) AS my_average
FROM
customers c 
LEFT JOIN orders o ON o.customerID = c.customerID AND o.date >= '2013-03-01' AND o.date < '2013-04-01'
LEFT JOIN items i ON o.itemID = i.itemID
GROUP BY c.customerID

I used the DISTINCT in the COUNT() function, because you might have multiple items per order. I don't know your data. Even if this is not the case, there's a difference between COUNT(*) and COUNT(whatever_column). The first counts every row, the latter counts only the rows that are NOT NULL in this column.

You have to use LEFT JOIN, because you want to have every customer, regardless if there is an order for the customer or not.

The filter condition for orders (only orders from March 2013) is in the join condition, because putting it in the WHERE clause would effectively turn the LEFT JOIN into an INNER JOIN (then you would only get the customers that have also ordered something in March 2013). Or you would have to extend your WHERE condition with OR o.orderID IS NULL.

Any more questions?