Question: Select the item and per unit price for each item in the items_ordered table. Hint: Divide the price by the quantity.
select item, sum(price)/sum(quantity)
group by item;
select item, price/quantity
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?.