
Question:
I have a products table, with the fields productname, category and cost, of type varchar, varchar and double.
I then have a sales table, with the fields productname, cost, and saledate, of type varchar, double and date.
Lastly, I have a purchases table with the fields purchase, cost and purchasedate, of type varchar, double and date.
I want to calculate the number of sales for certain dates, for certain categories.
I have at the moment this query to show the monthly totals for a year:
SELECT month(saledate), SUM(cost) FROM sales GROUP BY month(saledate) UNION ALL SELECT month(purchasedate), SUM(cost) FROM purchases GROUP BY month(purchasedate)
How could I restrict it to certain categories?
To define certain categories from products, such as food and drink, and only include products in these categories in my calculation?
Solution:1
SELECT month(saledate), SUM(sales.cost) FROM sales, products WHERE sales.productname=products.productname AND category='food' GROUP BY month(saledate) UNION ALL SELECT month(purchasedate), SUM(purchases.cost) FROM purchases, products WHERE purchases.productname=products.productname AND category='food' GROUP BY month(purchasedate)
The table needs to be part of the SQL statement in order to restrict the result-set rows based on some value held in it.
Solution:2
just add a WHERE clause like this one:
[...] WHERE products.category = 'food' OR prodcuts.category = 'drink' [...]
Solution:3
If you want your calculation result doesn't include these categories, you should use WHERE.
otherwise, using HAVING
Solution:4
I'd be inclined to use a subquery rather than a join, and I think you want it in both parts of the union. But Borealid's answer is quite sound. The difference is a matter of taste.
SELECT month(saledate), SUM(sales.cost) FROM sales, products WHERE EXISTS (SELECT * FROM products WHERE purchases.productname = products.productname AND category = 'food') GROUP BY month(saledate) UNION ALL SELECT month(purchasedate), SUM(purchases.cost) FROM purchases, products WHERE EXISTS (SELECT * FROM products WHERE purchases.productname = products.productname AND category = 'food') GROUP BY month(purchasedate)
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon