grouping common results in sql

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 want to show all of the products sold for a month, say the current month.

However, I don´t want to show every sale individually. I want to automatically add all of one product together and show it as one row.

In the sales table, the cost for each sales record is already multiplied by the quantity.

So for example, to if 5 beers were purchased, it would returned as one row showing name as beers, quanity as 5, and cost as however much.

I need something like say:

Select product, cost from sales, WHERE sales.product=products.name 
AND category='food' AND WHERE month(date_field) = month(getdate())

This should show all the sales for a certain category of product for the current month, but is there an easy way to "group" products together?

I would have to take into account the quantity field in the sales table, because one sale is not necessarily only one product

A hopefully clearer example, one sale record maybe for 2 beers for one client with a cost of 10, and another sales record may be to a different client with a quantity of 3 and cost of 15. I want just one record that would say for beer, 5 were sold and the total cost is 25.

I have no idea where to go from as far as I have gotten...

Answer 1

This question looks awfully familiar...

The answer is a SQL GROUP BY statement.

SELECT product, SUM(quantity) FROM sales s, products p WHERE product=p.name AND category='food' AND month(date_field)=month(getdate()) GROUP BY product

The SUM(quantity) above will tally up all the units sold of a particular product in the given month.

Answer 2

Use GROUP BY and COUNT

Answer 3

SELECT productname, cost, COUNT(*) AS cnt
FROM sales LEFT JOIN products ON sales.product = products.productname 
WHERE category='food' AND month(date_field) = month(getdate())
GROUP BY productname

Answer 4

You are looking for something like

Select product, cost from sales,products WHERE sales.product=products.name 
AND category='food' AND month(date_field) = month(getdate())

To get a listing of the items linked as you suggest according to the category in the products tables.

To get the summary by category you need something like:

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

This will work but there is a lot to criticise in your database structure, specifically to link products by name as you do is not very reliable.