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?

GravyMonster · 13 Mar 2009 at 17:05

It's hard to be specific without knowing your schema, but what you want is a count and some kind of group by.

Code:

SELECT pr.ProductID, SUM(pu.Quantity) AS NumSold FROM Products AS pr
INNER JOIN Purchases AS pu ON pr.ProductID = pu.ProductID
GROUP BY pr.ProductID
ORDER BY SUM(pu.Quantity) DESC;

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?

GarethDW · 13 Mar 2009 at 18:27

a field in one of your tables.... obviously Spunkey doesn't know what the tables and fields are really called cause he's not seen your schema, so you'll need to replace the relevant table and field names with whatever your teacher has defined (I'm assuming this is for your homework).

Bassmansam · 13 Mar 2009 at 18:41

It's 2nd year coursework.

Pho · 13 Mar 2009 at 20:25

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;

edit: MySQL safe version (I think?)

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 >= '2008-01-01' AND pu.date <= '2008-12-31'
GROUP BY pr.ProductID
ORDER BY SUM(pu.Quantity) DESC;

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.