Whenever you do a group by or order by operation, Oracle will invoke a sorting operation. Using the group by inside SQL syntax is also useful for doing summaries, rollups and cubic operations.
Grouping is a special type of sorting. With sorting, all the rows are sorted by the columns specified. Grouping sorts the rows into groups so that multi row functions can be specified at the group level.
Some related examples.
1. Use avg, sum, max and count functions with group.
SQL> select job_id, avg(salary), sum(salary), max(salary), count(*)
2 from employees
3 group by job_id
4 /
JOB_ID AVG(SALARY) SUM(SALARY) MAX(SALARY) COUNT(*)
----------- ----------- ----------- ----------- ----------
PROG 21000 84000 24000 4
DBA 20000 40000 20000 2
MGR 27500 55000 30000 2
3 rows selected.
2. Must include a nonaggregate column in the SELECT list in the GROUP BY clause.
SQL> SELECT City, MAX(Salary) AS "Highest Cost"
2 FROM Employee
3 GROUP BY City;
CITY Highest Cost
---------- ------------
New York 7897.78
Toronto 1234.56
Vancouver 6661.78
3. Grouping at Multiple Levels: group by more than one column.
SQL> SELECT count(*), city, description
2 FROM employee
3 GROUP BY city, description;
COUNT(*) CITY DESCRIPTION
---------- ---------- ---------------
1 New York Manager
4 Vancouver Tester
1 Toronto Programmer
1 Vancouver Manager
1 New York Tester
4. Column sequence in the group by impacts the ordering.
SQL> SELECT count(*), city, description
2 FROM employee
3 GROUP BY description, city;
COUNT(*) CITY DESCRIPTION
---------- ---------- ---------------
1 Toronto Programmer
4 Vancouver Tester
1 Vancouver Manager
1 New York Manager
1 New York Tester
SQL>
SQL> SELECT count(*), city, description
2 FROM employee
3 GROUP BY city, description;
COUNT(*) CITY DESCRIPTION
---------- ---------- ---------------
1 New York Manager
4 Vancouver Tester
1 Toronto Programmer
1 Vancouver Manager
1 New York Tester
5. Using the ORDER BY Clause to Sort Groups.
SQL> SELECT city, COUNT(city)
2 FROM employee
3 GROUP BY city
4 ORDER BY COUNT(city);
CITY COUNT(CITY)
---------- -----------
Toronto 1
New York 2
Vancouver 5
6. You don't have to include the columns used in the GROUP BY clause in your SELECT clause.
SQL> SELECT COUNT(description)
2 FROM employee
3 GROUP BY city
4 ORDER BY COUNT(id);
COUNT(DESCRIPTION)
------------------
1
2
5
7. GROUP BY and HAVING clauses.
SQL> SELECT city, AVG(salary)
2 FROM employee
3 GROUP BY city
4 HAVING AVG(salary) > 3000;
CITY AVG(SALARY)
---------- -----------
New York 6110.28
Vancouver 3823.78
8. Using a Column Multiple Times in a GROUP BY Clause.
SQL> SELECT division_id, job_id, SUM(salary)
2 FROM employee
3 GROUP BY division_id, ROLLUP(division_id, job_id);
DIV JOB SUM(SALARY)
--- --- -----------
BUS PRE 800000
SAL MGR 350000
SAL WOR 490000
SUP MGR 200000
BUS 800000
SAL 840000
SUP 200000
BUS 800000
SAL 840000
SUP 200000
10 rows selected.
