Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Combining aggregate and non-aggregate values in SQL using Joins and Over clause

  • Difficulty Level : Medium
  • Last Updated : 31 Aug, 2021

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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

NameNullType
EMPNONOT NULLNUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7, 2)
COMM NUMBER(7, 2)
DEPTNO NUMBER(2)

Table – DEPARTMENT TABLE

NameNullType
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(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:

1. 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 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

2. Using ‘Over’ clause –

  1. OVER CLAUSE ALONG WITH PARTITION BY IS USED TO BRAKE UP DATA INTO PARTITIONS.
  2. THE SPECIFIED FUNCTION OPERATES FOR EACH PARTITION.

Solutions for 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

 

My Personal Notes arrow_drop_up

Start Your Coding Journey Now!