Grouping fields by category and arranging by date, and calculating from another table

Question

I have a products table, with the fields product, category and cost, of type varchar, varchar and decimal.

I then have a sales table, with the fields client, productname, quantity, cost, and saledate, of type varchar, varchar, int, decimal and date.

I also have a purchases table, with the fields purchasedate, cost, quantity and purchase, of types date, decimal, varchar and varchar.

In my sales table, the cost field for a given record is automatically multiplied by quantity.

For example, if beer costs 10, and in a record for a sale of beer quantity is 2, the cost for that record will automatically be 20.

What I want to do, is return the result all sales and purchases automatically grouped.

For example, if I have the following data in the sales table:

productname - quantity - cost- saledate

beer          2          20    2010-07-10
beer          3          50    2010-07-11

And in the purchases table:

purchasedate - cost - quantity - purchase
2010-07-09     20     2          straws

I want to show the following output

Total sales for current month:

productname - quantity - cost

beer          5          50

Total purchases for current month

purchasedate - cost - quantity - purchase
2010-07-09     20     2          straws

I was trying something like the following:

Select category,SUM(Sales.Quantity),SUM(Sales.Quantity*cost) cost from 
sales,products WHERE sales.product=products.name 
AND category='food' AND month(date_field) = month(getdate()) group by category

However, this does not work first of all, and secondly I think it multiplies the cost field in the sales table a second time, making it inaccurate.

Would I have to include the original cost from the products table in order to do what I need?

Could I possible do what I want in one query, perhaps as a union, returning the respective rows and columns as per my example?

Answer 1

If I get your right, this should be

Total sales for current month:

SELECT s.productname, SUM(s.quantity), SUM(s.cost)
FROM sales AS s
LEFT JOIN products AS p ON s.productname  = p.name 
WHERE MONTH(s.saledate) = MONTH(NOW()) AND p.category = 'food'
GROUP BY s.productname

But I don't see the connection to the purchase table