1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL GROUP BY clause

Discussion in 'SQL PL/SQL' started by SBH, Oct 16, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Objective

    The article covers the GROUP BY clause in SELECT statement. It demonstrates the usage guidelines with examples.

    1. GROUP BY Clause – Definition

    GROUP BY allows developers to group the result set based on columns and produce aggregate results at the group level. In a SQL query, GROUPs are created as per the criteria specified in GROUP BY clause, and then filtering is done based on the conditions in WHERE clause.

    Syntax
    Code (Text):
    SELECT
    <COLUMN_LIST>, <GROUP BY FUNCTIONS>
    FROM <TABLE_NAME>
    WHERE <FILTER CONDITIONS>
    GROUP BY <COLUMN_LIST>
    HAVING <CONDITION BASED ON GROUP BY FUNCTIONS>
    Illustrations are as below


    Example 1.1 Below SQL query shows the sum of salary within the group formed by a department and job id i.e. for respective job ids in each department

    Code (SQL):
    SELECT
    DEPARTMENT_ID,
    JOB_ID,
    SUM (SAL)
    FROM EMP
    GROUP BY DEPARTMENT_ID, JOB_ID
    Above query is also depicted in the below figure.

    [​IMG]

    Example 2.2. Below query lists the count of employees working in each department.

    Code (SQL):
    SELECT
    DEPARTMENT_ID,
    COUNT (*)
    FROM EMPLOYEE
    GROUP BY DEPARTMENT_ID
    Note: Prior to Oracle 10g, GROUP BY clause also sorted the group result on the basis of columns specified, but after the release of Oracle 10g, Oracle introduced HASH GROUP BY mechanism. This mechanism did not sort the group records. For sorting purpose, an ORDER BY clause is mandatory.


    2. Guidelines of using GROUP BY clause

    • The columns based on which the result is expected to be aggregated, must appear in the GROUP BY clause.

    • This is because Oracle would group the result set based on GROUP BY specification, then apply the aggregating mechanism to arrive at the final result set. For example,

    Code (SQL):

    SQL> SELECT
    DEPARTMENT_ID,
    AVG(SALARY)
    FROM EMPLOYEE;
         
    DEPARTMENT_ID,
         *
    ERROR at line 2:
    ORA-00937: NOT a single-GROUP GROUP FUNCTION

    • GROUP BY clause does not support the use of column alias, but the actual names.

    Code (SQL):

    SQL> SELECT
    DEPARTMENT_ID DT,
    SUM(SALARY)
    FROM EMPLOYEE
    GROUP BY DT

    GROUP BY DT
             *
    ERROR at line 5:
    ORA-00904: "DT": invalid identifier
    • Group by columns may or may not appear in the SELECT list.

      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, oracle error message appears as “ORA-00979: not a GROUP BY expression”.


    Code (SQL):

    SQL> SELECT
    DEPARTMENT_ID,
    UPPER (ENAME)
    FROM EMPLOYEE
    GROUP BY DEPARTMENT_ID

         UPPER (ENAME)
               *
    ERROR at line 3:
    ORA-00979: NOT a GROUP BY expression

    • Aggregate function can’t be used in WHERE clause for filtering purpose.

    Code (SQL):

    SQL> SELECT
    DEPARTMENT_ID,
    SUM(SALARY)
    FROM EMPLOYEE
    WHERE SUM(SALARY) > 5000;

    WHERE SUM(SALARY) > 5000
          *
    ERROR at line 3:
    ORA-00934: GROUP FUNCTION IS NOT allowed here

    References
     

    Attached Files: