Average customer spending at each month and number of ordered items at each month

Question

I have 4 tables:

CUSTOMER (idCustomer,name,phone,email, markdown)
ORDER (idOrder,day,Customer_idCustomer)
ORDERITEM (idOrderItem, quantity, Order_idOrder, Product_idProduct)
PRODUCT (idProduct,name,price,description)

What would be the query for:

1. Average customer spending (for each customer) in individual months

2. Number of ordered items?

I already wrote some queries on this database.
Since I do it for Java application with JDBC, maybe I can split these two queries and make calculation of average separately but what is the best approach? Is there a way how to do it just with two SQL queries?

Thank you for help.

Query I use for price reduction:

String sql = """
        SELECT c.markdown, SUM(oi.quantity * p.price) 
        FROM orderitem oi, `order`o , product p , customer c
        WHERE c.idCustomer = o.Customer_idCustomer
        AND oi.Order_idOrder = o.idOrder
        AND oi.Product_idProduct = p.idProduct
        AND o.idOrder = " + idOrderValue + "
        GROUP BY oi.Order_idOrder 
        ;
        """;

Look here for database design (ERD)

Answer 1

You want an aggregation query with JOINs. Use proper JOIN syntax! Do not use commas in the FROM clause:

SELECT year(o.day) as yyyy,
       month(o.day) as mm,
       SUM(oi.quantity * p.price) / COUNT(DISTINCT c.idCustomer) as avg_customer_spending,
       SUM(oi.quantity) as num_items
FROM customers c JOIN
     orders o
     ON c.idCustomer = o.Customer_idCustomer JOIN
     orderitem oi
     ON oi.Order_idOrder = o.idOrder JOIN
     product p
     ON oi.Product_idProduct = p.idProduct 
GROUP BY year(o.day), month(o.day);