Incorrect usage of calling Aggregate functions

 

If the query contains an aggregate function and the selected columns are not in the aggregate function, these columns must be in the group by clause; otherwise the following error occurs: ORA-00937: not a single-group function. For example, the following query selects the product_type_id column and AVG (price), but does not use a group by clause containing product_type_id:

SQL> select product_type_id, AVG (price) 2 from products; Select product_type_id, AVG (price) * Error at line 1: ORA-00937: not a single-group Function

This error occurs because the database does not know how to process the product_type_id column in The result set. Consider: This query attempts to use the AVG aggregate function to operate on multiple rows of records, but tries to obtain the value of the product_type_id column from each row. These two operations cannot be completed at the same time. A group by clause must be provided to notify the database to group the rows in the same product_type_id column. Then, the database can pass the rows in these groups to the AVG function.

Warning:

If the query contains an aggregate function and the selected columns are not in the aggregate function, these columns must be in the group by clause.

Also, you cannot use aggregate functions in the WHERE clause to limit rows. If you do this, the following error occurs: ORA-00934: group function is not allowed here. For example:

SQL> select product_type_id, AVG (price) 2 from products 3 where AVG (price)> 20 4 group by product_type_id; Where AVG (price)> 20 * Error at line 3: The ORA-00934: group function is not allowed here

This error occurs because the WHERE clause can only be used to filter a single row rather than a row group. To filter row groups, you can use the having clause.