0

So I have a query that looks like this:

select name_of_restaurant, diners - avg(diners)
from table
group by name_of_restaurant;

name_of_restaurant is a VARCHAR(50) and diners is an INT.

what I am expecting it to do is this:

name_of_restaurant    diners - avg(diners)
merchant1                     -140
merchant2                     -200
merchant3                     -2

but instead I get:

name_of_restaurant    diners - avg(diners)
merchant1                     0.0000
merchant2                     0.0000
merchant3                     0.0000

How can I make it so that I get negative values in my result? What is wrong here? Thanks in advance for any assistance.

Giancarlo Manuel Guerra Salvá
  • 1,842
  • 3
  • 18
  • 35

1 Answers1

1

The GROUP BY expression that you're using here is malformed. diners is neither part of the grouping nor an aggregate function, so it's technically invalid to refer to it in the SELECT statement, as there may be multiple different values for that column in a single group. MySQL silently ignores this and uses an arbitrary value from the group.

(It's an unfortunate quirk of MySQL that this is even allowed. See "Why does MySQL allow "group by" queries WITHOUT aggregate functions?" for some discussion.)

In any case, from what you're describing here, I don't think you actually want a GROUP BY at all; what it sounds like you're trying to do is compare each row's diners with the overall average, not the average for that row or group. If that's the case, what you'd have to do is something along the lines of:

SELECT
     name_of_restaurant,
     diners - (SELECT AVG(diners) FROM table)
FROM table
Community
  • 1
  • 1