CODEWITHSUNDEEP
sqlsql-server
Phiter
Phiter

Reputation: 14992

Select top sold product from sales table

I have a sales table with those rows:

Sales table

cd_produto is the product code and qtd_produto is the quantity of the product that has been sold(daaah)

I want to get the top sold products. In this case it would be product #1 since it sums to 8 (1+7). The second would be #3 because it's 6.

If i run 

SELECT TOP 3 cd_produto, qtd_produto FROM itens_vendas ORDER BY qtd_produto DESC

I'll get them by top sales. But it would not sum the qtd for each individual product. It would consider 1 as the top sold but only because it's value is 7, and it will ignore the 1.

How do i order them by the SUM of each product?

Upvotes: 1

Views: 1450

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use group by and get the sum and use it in order by.

SELECT TOP 3 cd_produto, 
sum(qtd_produto) 
FROM itens_vendas 
GROUP BY cd_produto
ORDER BY sum(qtd_produto) DESC

Edit:

SELECT TOP 3 i.cd_produto, 
p.productname, sum(i.qtd_produto)          
FROM itens_vendas i 
join producttable p on i.cd_produto = p.product_id
GROUP BY cd_produto
ORDER BY sum(qtd_produto) DESC

Upvotes: 4

Related Questions