SQL Query

Bassmansam · 13 Mar 2009 at 16:49

Hi, Im having trouble with an SQL query

The query is : Find the most popular product sold over the past year

I have a table called Products which contains the stock , name of product.
And a a purchase table which records the purchases
And a customer table .

The realtionship is customer buys many products, so the purchases table is the link table.

Can anyone help?

Bassmansam · 13 Mar 2009 at 17:30

Thanks mate ill try that .

Bassmansam · 13 Mar 2009 at 17:56

Just one more question.. what is quantity?

Bassmansam · 13 Mar 2009 at 18:41

It's 2nd year coursework.

Bassmansam · 15 Mar 2009 at 15:57

Pho said:
You could use something like this to get orders within 2008-2009 (note: MSSQL syntax, not sure if it'll work on MySQL)

Code:

SELECT pr.ProductID, SUM(pu.Quantity) AS NumSold FROM Products AS pr INNER JOIN Purchases AS pu ON pr.ProductID = pu.ProductID WHERE pu.Date between '2008-01-01' AND '2008-12-31' GROUP BY pr.ProductID ORDER BY SUM(pu.Quantity) DESC;

[/code]

Thans a lot for that. Stupidly i didn't even have a quantity attribute in the purchases table. After adding that in it works great. Thanks a lot.