Which two are true about aggregate functions? (Choose two.)
A.
You can use aggregate functions in any clause of a SELECT statement.
B.
You can use aggregate functions only in the column list of the select clause and in the WHERE
clause of a SELECT statement.
C.
You can mix single row columns with aggregate functions in the column list of a SELECT
statement by grouping on the single row columns.
D.
You can pass column names, expressions, constants, or functions as parameter to an
aggregate function.
E.
You can use aggregate functions on a table, only by grouping the whole table as one single
group.
F.
You cannot group the rows of a table by more than one column while using aggregate
functions.
The A is wrong. You cannot use a group by function with WHERE clause
Correct. That’s why we have HAVING clause I believe. I think C & D is right answer.
Group function can appear in Where Clause. For example – list all employees have salary bigger than the average salary of company.
select employee_id, last_name, salary
from hr.employees
where salary > (select avg(salary) from hr.employees);
The Sql statement can run correctly!!!!!!!!!!!
This AVG function is in the select not in the where clause. I think the A is wrong, C and D are correct.
Group function can be used in Where clause. A simple example:
select *
from (
select department_id, avg(salary)
from hr.employees
group by department_id
);
I don’t understand why C is incorrect. According to my understanding, C means:
SELECT deptno, max(hiredate) FROM emp GROUP BY deptno;
deptno -> single row column
max(hiredate) -> aggregate function
GROUP BY deptno -> grouping on single row column.
Is it something that I didn’t get?
I agree.
The answer is C and D.
I agree.
So do I.
Thanks guys
Subqueries can be used in the SELECT list, but GROUP BY or aggregate functions are not allowed in the query if the subquery is not part of the GROUP BY clause in the containing query. For example, the following two statement returns an error message: