Which statement is true about using functions in WHERE and HAVING?
Which statement is true about using functions in WHERE and HAVING?
Aggregate functions cannot be used directly in the WHERE clause because they operate on groups of rows rather than individual rows. To filter results based on aggregate functions, it is necessary to use a subquery within the WHERE clause to first compute the aggregate values or use the HAVING clause, which is designed to filter groups based on aggregate values. Therefore, using aggregate functions in the WHERE clause does indeed require a subquery.
I thought aggregate functions cannot be used in the WHERE clause?
You are correct. In that case you would use HAVING
A select EmployeeId, sum(amount) from Sales group by Employee where EmployeeId in ( select max(EmployeeId) from Employees)
B is correct But I think C as well is correct
Example: select employee_id from employees where salary > (select avg(salary) from employees);
B is the Answer.
For me A is correct.
--B WRONG --select avg(salary) from emp group by dept_id having avg(salary) > 1000; --C WRONG --select last_name from emp where upper(last_name) like 'A%'; --D WRONG --Having is Used for Agg Functions. Single-row functions do not require grouping.