Search code examples
sqlpostgresqlhaving

Fields that not used in`GROUP BY` is not reachable in HAVING clauses

Fields that not used in GROUP BY are not usable in SELECT but they're usable in WHERE. This makes sense since WHEN comes before GROUP BY but shouldn't HAVING has to be able to access "other" columns of the row.

Example

Below is valid.

select fid, count(*)
from class
inner join faculty using (fid)
group by fid
having every(class.room = 'R128')

But can't do this.

select fid, count(*)
from class
inner join faculty using (fid)
group by fid
having class.room = 'R128' // Changed Line

Error message of above snippet:

RROR:  column "class.room" must appear in the GROUP BY clause or be used in an aggregate function
LINE 7: having class.room = 'R128'
               ^
SQL state: 42803
Character: 86

I didn't fall into XY Problem, I want to know why this is impossible (Question is correct with every() later is wrong in semantics too for the question)

Solution

  • having is used to filter the result of the grouping.

    However the room column is neither part of an aggregate nor part of the GROUP BY.

    every() is an aggregate function an thus it's allowed in the having clause.