GROUP BY Clause

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.