CODEWITHSUNDEEP
mysqlsql
Tuyen Nguyen
Tuyen Nguyen

Reputation: 155

How can I query this sql to get the best-selling product

This's my database sample: orderdetails table with the ProID relate to products tabale

| ProID | OrderID | OrderQuantity | OrderPrice |
|  93   |    17   |       1       |     150    |
|  16   |    18   |       1       |     100    |
|  93   |    19   |       3       |     450    |
|  93   |    17   |       1       |     150    |

products table

| ProID | ProPicture | ProName | ProPrice |
|  93   | ./a.jpg    |  Iphone |    150   |
|  16   | ./b.jpg    |  Nokia  |    100   |

How can I get the best-selling product information: ProID, ProPiecture, ProName, ProPrice from products table base on orderdetails table?

Upvotes: 3

Views: 24370

Answers (3)

Abdul Salam
Abdul Salam

Reputation: 238

You can try the following query -

select * from products where ProID =
(select ProID 
  from 
    (select ProID , sum(OrderQuantity) as total_order,
        max(sum(OrderQuantity)) over() as maxSm   from orderdetails
        group by ProID
    )
where total_order = maxSm)

Upvotes: 0

Abhay Narayan
Abhay Narayan

Reputation: 29

the Tested ms sql Query to Find Faster Selling product from above table

SELECT TOP(2) PRO.PRONAME, SUM(OD.ORDERQUANTITY) SALEQTY 
FROM PRODUCTS AS PRO INNER JOIN DBO.ORDERDETAILS AS OD 
  ON PRO.PROID = OD.PROID GROUP BY PRO.PRONAME

Upvotes: -1

stefancarlton
stefancarlton

Reputation: 1727

You can use a SQL join between the tables on ProID, e.g.

from products as p
inner join orderdetails as od
   on p.ProID = od.ProID

You can use group by syntax to ensure you get distinct rows, e.g.

group by p.ProID

Use aggregation function such as sum, count and avg to find out the totals in a select, e.g.

select sum(od.OrderQuantity) as total

Use order by syntax to show the top answers, e.g.

order by sum(od.OrderQuantity) desc

Use limit to show top n results, e.g.

limit 5

Hopefully that gives you enough pointers to formulate the SQL yourself.

notes, I've given the tables an alias to ensure you don't get conflicts between the column names. You can't reference total in your order statement due to the way SQL calculates the dataset. I've used an inner join, but you might want to look into left & right joins.

Upvotes: 6

Related Questions