0

For this query, I'm trying to average the price of all orders for a given restaurant. It works perfectly fine for the sum:

SELECT r.restaurantName, SUM(m.price) AS 'totalPrice w/o Tax', SUM(m.price *1.10) AS 'totalPrice w/ Tax'
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN FoodOrder f
ON m.itemNo = f.itemNo
WHERE r.restaurantName = "Eureka Pizza";

But when I try to average that I get an error:

SELECT r.restaurantName, SUM(m.price) AS 'totalPrice w/o Tax', SUM(m.price *1.10) AS 'totalPrice w/ Tax', AVG(SUM(m.price)) AS 'avg base price'
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN FoodOrder f
ON m.itemNo = f.itemNo
WHERE r.restaurantName = "Eureka Pizza";

1 Answer 1

2

You just need AVG(m.price) in your select list.

5
  • Oh ok. So it does a SUM itself?
    – Razmode
    Commented Mar 4, 2017 at 23:32
  • 1
    Yup. Try it and see... Think of it a bit like MS Excel. If you want the average of a column, you just use the average function; you don't need to sum it first.
    – BobC
    Commented Mar 4, 2017 at 23:33
  • I tried it, it works perfectly.
    – Razmode
    Commented Mar 4, 2017 at 23:34
  • That makes sense. I just assumed that I needed to nest them.
    – Razmode
    Commented Mar 4, 2017 at 23:36
  • 1
    No. There are a whole bunch of "aggregate" functions like this :) Of course, you could do it yourself - then it would be "sum(price) / count(*)" - but there is generally no need :)
    – BobC
    Commented Mar 4, 2017 at 23:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.