问题描述:

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.

网友答案:

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
网友答案:

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?.

相关阅读:
Top