CODEWITHSUNDEEP
mysqlsqlselect
DaViDa
DaViDa

Reputation: 641

A way to count with column items

I have an ordered_products table which has all the ordered products in it. Lets say I have a product called trouser1. When I have for example order1 and order2 and both those orders have trouser1 in it adds to ordered_products:

order_id=1 name=trouser1 amount=1
order_id=2 name=trouser1 amount=1

Now I want to make a top 10 list of sold products but the same product needs to display once in my table but with the amount added together.

I tried something like the following:

SELECT *
FROM ordered_products
GROUP BY naam
ORDER BY aantal DESC
SUM(amount)
WHERE name NOT UNIQUE

It doesn't work, is there some way to do this?

Upvotes: 1

Views: 85

Answers (2)

Dcortez
Dcortez

Reputation: 4238

Select top 10 name, sum(amount)
from ordered_products
group by name
order by sum(amount ) desc

It should work. ;) You just have to display sum you are counting. Not just order by it. * wont't do it for you.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

this will show the top 10 list of sold products,

SELECT  name, SUM(amount) totalAmount
FROM    ordered_products
GROUP BY name
ORDER BY totalAmount DESC
LIMIT 10

for better performance, provide an index on column name.

Upvotes: 3

Related Questions