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

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

Name Null Type
EMPNO NOT NULL NUMBER(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

Name Null Type
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: