This is for a class Database is setup with 4 tables of interest. Employees, customers products, Which have basic employee customer and product info, Orders table, which is very simple, it has the order number Primary key, employee who took the order, and the customer the order is for. Then an order details table, that has the order number, product id and quantity of that product ordered and the date it was ordered.
I’m trying to figure out how to do a query to get the gross sales by product by month, but I can’t figure it out.
customers.fNAME, customers.lname, products.discription, products.price, orderdetails.quantity, orders.id, (products.price*orderdetails.quantity) as total, orders.placedon
FROM customers, orders, orderdetails, products
WHERE customers.ID = ORDERS.BYCUSTOMER and ORDERS.ID=ORDERDETAILS.ORDERID and products.id=ORDERDETAILS.PRODUCTID
This query lets me get the totals on each line of the orders, which I know is the starting point. From here I need to be able to aggregate it by order number, then hopefully I can figure out the sort by customer/product/date.