Skip to content
Related Articles

Related Articles

Combining aggregate and non-aggregate values in SQL using Joins and Over clause
  • Difficulty Level : Medium
  • Last Updated : 07 Aug, 2018

Prerequisite – Aggregate functions in SQL, Joins in SQL
Aggregate functions perform a calculation on a set of values and return a single value. Now, consider an employee table EMP and a department table DEPT with following structure:


Table – EMPLOYEE TABLE
NameNullType
EMPNONOT NULLNUMBER(4)
ENAMEVARCHAR2(10)
JOBVARCHAR2(9)
MGRNUMBER(4)
HIREDATEDATE
SALNUMBER(7, 2)
COMMNUMBER(7, 2)
DEPTNONUMBER(2)


Table – DEPARTMENT TABLE
NameNullType
DEPTNONUMBER(2)
DNAMEVARCHAR2(14)
LOCVARCHAR2(13)



And the following results are needed:

  1. DISPLAY NAME, SAL, JOB OF EMP ALONG WITH MAX, MIN, AVG, TOTAL SAL OF THE EMPS DOING THE SAME JOB.
  2. DISPLAY DEPTNAME WITH NUMBER OF EMP WORKING IN IT.

The aggregated values can’t be directly used with non-aggregated values to obtain a result. Thus one can use the following concepts:

  • Using Joins –
    1. Create a sub-table containing the result of aggregated values.
    2. Using Join, use the results from the sub-table to display them with non-aggregated values.

    Solutions for the problem 1 using JOIN:

    SELECT ENAME, SAL, EMP.JOB, 
                SUBTABLE.MAXSAL, SUBTABLE.MINSAL, 
                SUBTABLE.AVGSAL, SUBTABLE.SUMSAL
    FROM EMP
    INNER JOIN
            (SELECT JOB, MAX(SAL) MAXSAL, MIN(SAL) 
                    MINSAL, AVG(SAL) AVGSAL, SUM(SAL) SUMSAL 
             FROM EMP 
              GROUP BY JOB) SUBTABLE
                      ON EMP.JOB = SUBTABLE.JOB; 

    Output for sample data:

    EnameSalJobMaxSalMinSalAvgSalSumSal
    SCOTT3300ANALYST330019252841.678525
    HENRY1925ANALYST330019252841.678525
    FORD3300ANALYST330019252841.678525
    SMITH3300CLERK330010451746.256985
    MILLER1430CLERK330010451746.256985

  • Using ‘Over’ clause –
    1. OVER CLAUSE ALONG WITH PARTION BY IS USED TO BRAKE UP DATA INTO PARTITIONS.
    2. THE SPECIFIED FUNCTION OPERATES FOR EACH PARTITION.

    Solutions for the problem 2 using OVER Clause:

    SELECT DISTINCT(DNAME),
    COUNT(ENAME) OVER (PARTITION BY EMP.DEPTNO) EMP
    FROM EMP
    RIGHT OUTER JOIN DEPT
    ON EMP.DEPTNO=DEPT.DEPTNO
    ORDER BY EMP DESC; 

    DnameEmp
    SALES6
    RESEARCH5
    ACCOUNTING3
    OPERATIONS0
    OTHERS0

    Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

    My Personal Notes arrow_drop_up
Recommended Articles
Page :