AVG , group by, WHERE AVG greater (>) issue

Question

this is my database

CREATE TABLE korisnici(
    name VARCHAR(30) NOT NULL,
    amount DECIMAL(65,2)
    );

INSERT INTO korisnici VALUES 
("Marina",20.10),
("Petar",300.50),
("Ivana",100.70),
("Tomislav",50.20),
("Ivana",80.60),
("Petar",10.40),
("Marina",80.50),
("Ivana",70.50),
("Marina",130.20),
("Robert",60.20),
("Blanka",130.20),
("Blanka",220.40),
("Tomislav",150.20);

I would like to fetch all names from list which has average ammount of all their amounts greater than 150. Something like I tried

SELECT name, AVG(amount) AS avg FROM `korisnici` WHERE avg > 150 GROUP BY name

However my query fails, with error "Unknown column 'avg' in 'where clause'". Can someone give me a hint.

D Stanley · Accepted Answer · 2015-09-15T12:45:41.857

11

You can't use a column alias in a WHERE, JOIN, or HAVING clause, so you need to repeat the expression, but that's not the only problem. When filtering on the result of an aggregation, the HAVING clause should be used instead of WHERE:

SELECT name, AVG(amount) AS avg 
FROM `korisnici` 
GROUP BY name
HAVING AVG(amount) > 150

The reason is that the WHERE clause is applied before the grouping and aggregation (and is used to determine which records get grouped and aggregated), while HAVING is applied after the aggregation.

edited Sep 15 '15 at 12:45

answered Sep 14 '15 at 22:11

D Stanley

149,601
11
178
240

Yes, that fixes the *query*, but most DBs won't let you refer to a column alias in the having/where clause - you have to repeat the expression. That's the *actual* cause of the error *`Unknown column 'avg' in 'where clause'`* – Bohemian Sep 14 '15 at 22:12
@Bohemian Most might not, but given that MySQL does, I can't quite fathom your point – Strawberry Sep 14 '15 at 22:36
@Strawberry My point is, just moving the condition to a HAVING clause will not fix the error in most dbs; you have to *also* use the raw expression in the condition (and not its alias). I don't know what relevance mysql has here. – Bohemian Sep 15 '15 at 03:33
@Bohemian I don't know either. I must have imagined it!! – Strawberry Sep 15 '15 at 08:30
@Bohemian _Just_ changing the `WHERE` clause to use the expression would have resulted in a syntactically proper query but not the proper results. I have clarified that in my answer. – D Stanley Sep 15 '15 at 12:44

score -4 · Answer 2 · answered Sep 14 '15 at 22:21

-4

You can not write like that: it is a common SQL error.

avg is the identifier and you can not use an identifier in the where clause..

    SELECT name, AVG(amount) AS avg 
    FROM `korisnici` 
    WHERE AVG(amount) > 150 GROUP BY name;

There you go..

answered Sep 14 '15 at 22:21

satish kumar Parida

27
1
3
10

AVG , group by, WHERE AVG greater (>) issue

2 Answers2

Linked