CODEWITHSUNDEEP
sqlhaving
AlexH
AlexH

Reputation: 1127

Can a HAVING clause of an SQL query use ONLY aggregate functions?

Our database instructor's lecture slides on HAVING and GROUP BY make this claim:


enter image description here

Unfortunately, because the SEO around this topic is so saturated, it's nearly impossible to find a source that answers my question. I can find plenty of examples where HAVING is indeed used with aggregates, but does that necessarily imply that it MUST use aggregates?

Based on my understanding and experience, the HAVING clause need not only use grouping attributes or attributes that appear in aggregates (even though it can, unlike its counterpart WHERE).

As I understand it, HAVING was created because WHERE can't operate at the group level.

So, what exactly is stopping me from using any other conditions in a HAVING clause, such as a plain old HAVING attribute = value condition, where attribute appears in neither the GROUP BY nor an aggregate in the SELECT clause? Visually speaking, it makes perfect sense for me to say:

"Pick only those groups where there's a row that has this value under this column."

Upvotes: 0

Views: 4919

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

HAVING is like a WHERE clause that takes place after aggregation. The following two queries are functionally identical (assuming the ellipses are compatible):

select key1, key2, . . . 
from t
group by key1, key2
having . . .

and:

select x.*
from (select key1, key2, . . . 
      from t
      group by key1, key2
     ) x
having . . .

The having clause can refer to:

  • Aggregated values from the select.
  • keys from the group by.
  • Functions that take no argument (such as the current date).
  • Expressions composed of the above.

It cannot refer to unaggregated columns that are not keys. Such columns simply do not exist in the result set produced by the group by.

There is one exception to this, which I explain at the risk of confusion. A few databases support columns which are functionally dependent as group by keys. What this means in English is that if the query aggregated by unique/primary keys, then other columns from that table can be used with not aggregation functions. However, this is an extension to the general rule that the references in the having clause are only those available after the aggregation.

Upvotes: 3

Related Questions