The Oracle PL/SQL GROUP BY clause enables establishing data groups based on columns.
The grouping criterion is defined by the GROUP BY clause, which follows the WHERE clause in the SQL clause hierarchy.
Following this hierarchy, result set rows are grouped based on like values of grouping columns and the WHERE clause restricts the entries in each group.
SYNTAX
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;
Notes:
- All the columns used besides the aggregate functions must be included in the GROUP BY clause.
- The GROUP BY clause does not support the use of column aliases- you must use the actual column names.
- The GROUP BY columns may or may not appear in the SELECT list.
- The GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN.
- If it is used with single row functions, the Oracle error message appears as "ORA-00979: not a GROUP BY expression".
Example for Group by clause | Group by examples
For
example we want to see deptname and average salary of them.
Select
dname,avg(sal) from emp,dept
where emp.deptno=dept.deptno group by dname;
Similarly
to see sum of sal.
Select
dname,sum(sal) from emp,dept
where
emp.deptno=dept.deptno group by dname;
Now we
want to see the cities name and the no of employees working in each city.
Remember emp and dept are joined on deptno and city column is in the dept
table. Assuming that wherever the department is located the employee is working
in that city.
Select
dept.city,count(empno) from emp,dept
where emp.deptno=dept.deptno
Group by dept.city;