My tables are: customer(cid, name, city, state)
orders(oid, cid, date)
product(pid, productname, price)
lineitem(lid, pid, oid, number, totalprice)
I want to create a view ProductMonth (PID, ProductName, Month, QuantitySold, NumberCus­tomer, Revenue) that gives for each the product and each month, the quantity sold, the number of distinct customers who bought the product and amount of money the sale corresponds to for the month.
I used extract() to get the months from orders. date. I need to know how to loop each month and each product. I couldn't come up with a working query. Is it even possible to do it in MySQL? I mean looping the product name for each month and calculating the total price and also how much each product sales contributed to the whole. I know how to implement this for a particular product or month but not for products sold in each month. I tried using joins but it fails. I need the query and also a logic to implement it.