[SOLVED] Using SQL to find the total number times a product has been bought in one query

Issue

I’m trying to find the most optimal way to return a recordset containing a list of all products that have been bought by customers and how many of each have been sold.

Table ORDERS is laid out like this:

CustomerID ProductID (etc)
1              10
1              10
1              11
2              10
4              9

This is a many to many relationship, so the same customer can have multiple records and can buy the same product multiple times. I need to get a recordset containing the following info, using a query I can put in a stored procedure:

ProductID NumSales
1            3
2            1
4            1

Any help would be appreciated – I’m fairly new to advanced SQL, and I’d love it if you could give me a basic rundown of how the query works so I can learn to do it myself in the future – I have the feeling it’s fairly simple, but I just don’t know the syntax yet!

Solution

select ProductID, count(*) as NumSales from Orders group by ProductID

If there’s a quantity field there too, try this:

select ProductID, sum(Quantity) as TotalQty from Orders group by ProductID

Answered By – Jonathan Rupp

Answer Checked By – David Marino (BugsFixing Volunteer)