Why are aggregate functions not allowed in where clause

by

The reason you can’t use SUM() in the WHERE clause is the order of evaluation of clauses.

FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be read from disk. “Conditions” are formally known as predicates and some predicates are used – by the query execution engine – to decide which rows are read from the base tables. These are called access predicates.) As you can see, the WHERE clause is applied to each row as it is presented to the engine.

On the other hand, aggregation is done only after all rows (that verify all the predicates) have been read.

Think about this: SUM() applies ONLY to the rows that satisfy the WHERE conditions. If you put SUM() in the WHERE clause, you are asking for circular logic. Does a new row pass the WHERE clause? How would I know? If it will pass, then I must include it in the SUM, but if not, it should not be included in the SUM. So how do I even evaluate the SUM condition?

More Related Contents:

  1. LISTAGG in Oracle to return distinct values
  2. How do I limit the number of rows returned by an Oracle query after ordering?
  3. How to create id with AUTO_INCREMENT on Oracle?
  4. When do I need to use a semicolon vs a slash in Oracle SQL?
  5. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  6. How to handle a single quote in Oracle SQL
  7. Select count(*) from multiple tables
  8. Fetch the rows which have the Max value for a column for each distinct value of another column
  9. REGEX to select nth value from a list, allowing for nulls
  10. Concatenate and group multiple rows in Oracle [duplicate]
  11. Difference between BYTE and CHAR in column datatypes
  12. How to use BOOLEAN type in SELECT statement
  13. Call a stored procedure with another in Oracle
  14. create table with sequence.nextval in oracle [duplicate]
  15. Oracle: loading a large xml file?
  16. IF EXISTS condition not working with PLSQL
  17. Group by alias (Oracle)
  18. Grouped string aggregation / LISTAGG for SQL Server
  19. Sleep function in ORACLE
  20. Split comma separated values of a column in row, through Oracle SQL query
  21. oracle — Split multiple comma separated values in oracle table to multiple rows
  22. GROUP BY without aggregate function
  23. update columns values with column of another table based on condition [duplicate]
  24. how to select even records from a table in oracle?
  25. Oracle : select maximum value from different columns of the same row
  26. Split varchar into separate columns in Oracle
  27. Creating or simulating two dimensional arrays in PL/SQL
  28. How to retrieve the current value of an oracle sequence without increment it?
  29. Oracle date “Between” Query
  30. Select rows until condition met

Leave a Comment