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!
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)