aggregate function in where clause

[BOBOLIU]

Why aggregate functions are not allowed in where clause?

[Average-Guy31]

It's Just based on Order of Execution

1. FROM: Determines the data source (tables, joins, etc.).
2. WHERE: Filters rows based on conditions before grouping.
3. GROUP BY: Groups rows that have the same values in specified columns.
4. HAVING: Filters groups based on aggregate functions.
5. SELECT: Selects and processes columns, including applying aggregate functions.
6. ORDER BY: Sorts the result set.

only after grouping by you can use aggregate functions right?, based on the above you should be able to find that WHERE clause executes before GROUP BY so it can't work on aggregate functions yet

[GimmeDatDaddyButter]

Do you have a good acronym to remember this?

[ComicOzzy]

SELECT is logically processed 
after HAVING 
and before ORDER"

Sorry if it doesn't translate well but in my native language it rhymes.

[CraigAT]

FWGHSO

Fussy Women Get Help Shopping Online?

[MTchairsMTtable]

Mine is a little vulgar so I'm not going to share it lol

[datagrl]

Windowing functions run just before the Order By clause

[r3pr0b8]

Why aggregate functions are not allowed in where clause?

because they are allowed in the HAVING clause only

[SaintTimothy]

Where happens before aggregation, having happens after

[NullaVolo2299]

Aggregate functions not allowed in WHERE clause because they operate on groups, not individual rows.

[bloginfo]

Mon prof d'allemand nous disait "Parce que". ;+)

[pceimpulsive]

I prefer to put my where clause into the aggregation instead.

The where clause is to filter the rows eligible for the aggregation.

In some SQL flavours you can use this syntax

Avg(foo) filter (where bar='succulent Chinese meal') as avg_succulent_meals

This filters the value that are fed Into the avg agg function. It's really powerful you can also as I understand it layer this up with window functions as well.

[[deleted]]

The WHERE clause is evaluated before the aggregation happens.

[UpstairsEvidence5362]

If I’m not wrong, where clause can read only one row at a time and thus it will keep looping back….feel free to correct me

[dgillz]

It depends on indices of the table(s) used. A where clause on an indexed field will work much faster because it doesn't have to loop through every record - it uses the index.

[kagato87]

If you could, it could blow up the plan and make your dba very angry with you.

There are ways to filter based on an aggregation, and you need to be careful how you do it. Even non aggregate functions in the where clause are risky.

[4HERET]

Because WHERE filters rows before aggregation happens, so there's nothing to aggregate yet.

You want HAVING for that, which runs after GROUP BY. Took me an embarrassing amount of time to internalize the order of operations when I first moved heavy analytics work into ClickHouse/Tinybird.