SQL - HAVING Clause in depth

Question

I'm trying to understand what condition can be placed after the having clause - it is usually a condition, that uses the functions from the select statement, but it also can contain a column, that is typed in the select statement.

Here's an example:

create table r(a int, b int, c int, d int, e int primary key)

select a, min(b), sum(c)
from r
where b > 5
group by a
having "condition"

which of the following can replace the "condition"

a) b = 5

b) a = sum(e)

c) min(b) >= 6

When I execute this in SQL, only a) doesn't work, but what's the logic behind it?

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. Reference https://www.w3schools.com/sql/sql_having.asp Hence the case a will not work as that is not aggregate function — DevelopmentIsMyPassion, Jul 05 '17 at 10:20
HAVING filters to eliminate the results of an aggregated group, the expression `b = 5` is not valid because `b` is not aggregated or grouped. — Alex K., Jul 05 '17 at 10:21
ok, it think it is clearer now - for example a = 2 works, because we're grouping by a — Rio, Jul 05 '17 at 10:31

score 0 · Answer 1 · answered Jul 05 '17 at 10:19

0

This will be correct one

create table r(a int, b int, c int, d int, e int primary key)

select a, min(b), sum(c)
from r
where b > 5
group by a
having "min(b) >= 6"

answered Jul 05 '17 at 10:19

Nithin

1,376
12
29

score 0 · Answer 2 · answered Jul 05 '17 at 10:30

The HAVING clause is applied as a filter after the GROUP BY.

If you keep that in mind, it becomes obvious that:

b=5 fails because you don't GROUP BY b in the query and b is used as-is and not in an aggregation function.

a=SUM(e) succeeds because you GROUP BY a and compare a with an aggregate function.

MIN(b) >= 6 succeeds because you compare an aggregate function with a constant.

Hope this clarifies ...

SQL - HAVING Clause in depth

2 Answers2