[Solved] Whats the difference between these two SQL queries?


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

solved Whats the difference between these two SQL queries?