1

Hi I have a table called items which looks as follow:

items table

itemname | itemprice
---------------------
orange   | 0.50
pear     | 0.40
apple    | 0.40
banana   | 0.75

I also have a table that records each sale:

sales table

date     | itemname | qty
---------------------
17/12/13 | orange   | 4
22/12/13 | banana   | 6
23/12/13 | banana   | 2
29/12/13 | pear     | 3
02/01/14 | orange   | 2
05/01/14 | pear     | 6
05/01/14 | banana   | 5
06/01/14 | apple    | 2

What I want to do is get the itemname and price of the 2 most sold items. So the query should return:

itemname | itemprice
---------------------
banana   | 0.70
pear     | 0.40

I can get the 2 most common items but that is wrong as the qty needs to be taken into account and not the frequency of the itemname in the sales table.

Patrick Q
  • 6,373
  • 2
  • 25
  • 34
M9A
  • 3,168
  • 14
  • 51
  • 79

1 Answers1

3
SELECT i.itemname, i.itemprice
FROM items i
JOIN sales s ON i.itemname = s.itemname
GROUP BY s.itemname
ORDER BY SUM(s.qty) DESC
LIMIT 2

SQL Fiddle

If you want to be ANSI-compliant, change the GROUP BY to

GROUP BY i.itemname, i.itemprice
Patrick Q
  • 6,373
  • 2
  • 25
  • 34
  • I would highly advise against using non-ANSI `GROUP BY` syntax – Kermit Feb 20 '14 at 22:32
  • @FreshPrinceOfSO Okay, can you explain why? – Patrick Q Feb 20 '14 at 22:36
  • [Why does MySql allow “group by” queries WITHOUT aggregate functions?](http://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions) – Kermit Feb 20 '14 at 22:44
  • @FreshPrinceOfSO I was hoping for more of an explanation as to why you personally wouldn't use it. Yes, it is in violation of the ANSI standard. But what do you see as the downsides? The link you posted doesn't really go into why it would be bad. It's more just people bickering about why MySQL would break the ANSI standard. As long as you understand what MySQL is doing and that you can't predict/control the value of the non-aggregated field (and you don't need to in this case), the only downside I would see is that it could create a portability issue. – Patrick Q Feb 21 '14 at 14:09
  • It's terrible practice to use a platform specific feature that doesn't guarantee what columns will actually get aggregated. If that isn't enough, then portability *is* a huge issue. Would you like to rewrite queries if you move to any platform *other* than MySQL? Thank you for improving your answer, although all you're doing now is getting the `DISTINCT` result. – Kermit Feb 21 '14 at 14:20
  • @FreshPrinceOfSO Fair enough. Care to provide your solution? – Patrick Q Feb 21 '14 at 14:29
  • It would require a solution with a derived table since MySQL doesn't have window functions. To illustrate my point, [here's your query in SQL Server](http://sqlfiddle.com/#!3/ca77a/1). – Kermit Feb 21 '14 at 16:43