CODEWITHSUNDEEP
mysqlsqlsubqueryaggregate-functionshaving
Harryc
Harryc

Reputation: 9

Aggregate functions in having

I understand why aggregate functions have to be used in the having part of a query, by do not understand the reasoning why the two queries bellow return different values ie. what each of the functions is returning:

SELECT col1 FROM tbl
HAVING col1 > AVG(col1);

SELECT col1 FROM tbl
HAVING col1 > SELECT(AVG(col1) FROM tbl);

Upvotes: 0

Views: 1175

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

This query has an aggregation function:

SELECT col1
FROM tbl
HAVING col1 > AVG(col1);

Hence, it is an aggregation query with no group by and it returns one row. The question is: what is the value of col1 in the HAVING clause.

Well, MySQL has extended SQL to allow this syntax. It takes an arbitrary value of col1 from an indeterminate row. So, it arbitrarily compares some value of col1 to the average. That is probably not what you want. Note: This syntax would fail in almost any other database.

The second query:

SELECT col1
FROM tbl
HAVING col1 > SELECT(AVG(col1) FROM tbl);

has not aggregation for the outer reference to tbl. So, it is using a different MySQL extension. In this case, the HAVING is equivalent to WHERE:

SELECT col1
FROM tbl
WHERE col1 > (SELECT AVG(col1) FROM tbl);

(Note that the parentheses are fixed.)

And this does what you want. The WHERE version is probably what you intend and will work in any database.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

here in the 1st query though in mysql syntactically does not throw error but logically it is meaning less . note:This query will be thrown error in sql server though syntax right

  SELECT col1 FROM tbl
    HAVING col1 > AVG(col1);

2nd query generate complete avg values of col1 for whole table in sub-query and compare this value with col1

    SELECT col1 FROM tbl
    HAVING col1 > SELECT(AVG(col1) FROM tbl);

Thats why you got the two different types of output

Upvotes: 0

Related Questions