What if I have table like this and I want to select the best selling product_id.
| id | transaction_id | product_id | qty_sold |
|---|---|---|---|
| 1 | 21 | 2 | 5 |
| 2 | 22 | 3 | 2 |
| 3 | 23 | 4 | 2 |
| 3 | 24 | 2 | 1 |
| 3 | 25 | 2 | 4 |
I want the best selling product_id with the highest qty_sold
CodePudding user response：
Using SQLS, you can group by the productID, add up the number of sold, and order by the total descending. If we also take the minimum transaction ID per product, if two products come out to have the same total qty, we can take the minimum tran ID to split the tie
SELECT TOP 1 product_id, SUM(qty_sold) as sellcount, MIN(transaction_id) as firsttran
FROM t
GROUP BY product_id
ORDER BY SUM(qty_sold) DESC, MIN(transaction_id)
Once you're happy the sums are right etc, you can remove the , SUM(qty_sold) as sellcount, MIN(transaction_id) from the SELECT if you want/if you only need the prod ID