CODEWITHSUNDEEP
javamysqlsqlitems
Thor Christensen
Thor Christensen

Reputation: 83

Most bought item in store SQL

Hi im currently trying to make a school project saving orders from a pizzashop in mySQL. I want to sort those orders to find the most bought pizza and how many of that pizza that has been sold.

My tables look like this.. Table with the order id, the nr on the pizza sold and how many of that pizza sold on one order

I tried running something like this.. SELECT nr as mostbought, qty FROM odetails GROUP BY nr ORDER BY SUM(qty) DESC LIMIT 5;

But it doesnt add up the qty, as nr 5 and 1 is bought 6 times and not 3 and 2.. With the query i wrote it seems to output the highest qty of the most bought..

Table with the nr of the pizza bought most and qty(which i want added together)

I hope someone can help us, we are kinda stuck here.. Thanks alot :)

Upvotes: 0

Views: 231

Answers (2)

Andrew
Andrew

Reputation: 1595

You are just asking it to give you the quantity from that row, not the SUM of the quantities in the group:

SELECT nr AS mostbought, SUM(qty) AS qtysum
FROM odetails
GROUP BY nr
ORDER BY qtysum DESC
LIMIT 5;

This will sum the quantities within the group, grouped by the unaliased column nr, ordered by the sum of quantities, using the alias given for that, qtysum. I avoided using the name qty again, since you should avoid using columns that exist in your datasource as alias names, it can confuse the database.

I made a quick example of this here, though I don't know what you nr column actually is, so I just used letters, it will work just as well with any data type there: SQLFiddle

Upvotes: 0

Greg Patnude
Greg Patnude

Reputation: 49

SELECT nr as mostbought, SUM(qty) FROM odetails GROUP BY 1 ORDER BY 2 DESC LIMIT 5;

Upvotes: 1

Related Questions