-5

Question: Select the item and per unit price for each item in the items_ordered table. Hint: Divide the price by the quantity.

1.

select item, sum(price)/sum(quantity)
from items_ordered
group by item;

2.

select item, price/quantity
from items_ordered
group by item;

Have a look at the resultis for flashlights. First one shows average price correctly but 2nd one only takes 28/4 and shows 7, ignoring the 4.5 few rows down. Someone please explain why this is the case.

The used table data from an external website.

Dag
  • 10,079
  • 8
  • 51
  • 74
kalin
  • 155
  • 9
  • 3
    I'm assuming this is MySQL? Most RDBMS' would raise an error... – Ben Apr 16 '15 at 12:37
  • The first one will return a result and the second one will raise an error. From what I understand you should use the second one but remove the group by. – Zohar Peled Apr 16 '15 at 12:40
  • First query you divide the sum of price in _all_ rows by the sum of quantity in _all_ Rows. Second Query you divide the price of the _current_ row by the quantity of the _current_ row. – Dlanod Kcud Apr 16 '15 at 12:43
  • You haven't even created the table and tested the two queries? Lazy students don't learn much... – jarlh Apr 16 '15 at 12:59

2 Answers2

1

SUM() is a group function - so that essentially says go get me all the price and quantities by item, and add them all up to return them in one row.

MySQL is quite forgiving when grouping things and will try to retrieve a rowset (which is why your second example returns something - albeit wrong). Generally, if you are GROUPing columns (items in your exmaple), you need to return one row per column (item).

Try running the SQL below to see what that looks like.

SELECT item
     , SUM(price) AS sum_price
     , SUM(quantity) AS sum_quantity
     , COUNT(*) AS item_count
     , SUM(price) / SUM(quantity) AS avg_price_per_quant
  FROM items_ordered
 GROUP BY item
 ORDER BY item ASC
ash
  • 1,224
  • 3
  • 26
  • 46
0

The first query returns the average price for that item, the second query returns the price for the first item it encounters. This only works in MySQL, the second query would error in SQL Server as no aggegrate function is used. See this post for more details Why does MySQL allow "group by" queries WITHOUT aggregate functions?.

Community
  • 1
  • 1
Alex
  • 21,273
  • 10
  • 61
  • 73