CODEWITHSUNDEEP
mysqlsql
user1633170
user1633170

Reputation: 11

Filtering data in sql for each month from a date field

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.

Upvotes: 1

Views: 754

Answers (1)

Ronson
Ronson

Reputation: 26

This assumes that lineitem.number is the number of units sold.

SELECT  P.pid
,p.productname
,Month=datepart(mm, O.date)
,QuantitySold = sum(L.number)
,NumberCustomer = count(distinct cid)
,Revenue = sum(L.totalprice)
FROM orders O 
JOIN customer C on C.cid = O.cid
JOIN lineitem L on L.oid = O.oid
JOIN product P on P.pid = L.pid
GROUP BY
 P.pid
,p.productname
,Month=datepart(mm, O.date)

Upvotes: 1

Related Questions