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:
- DISPLAY NAME, SAL, JOB OF EMP ALONG WITH MAX, MIN, AVG, TOTAL SAL OF THE EMPS DOING THE SAME JOB.
- 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 –
- Create a sub-table containing the result of aggregated values.
- 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:
Ename |
Sal |
Job |
MaxSal |
MinSal |
AvgSal |
SumSal |
SCOTT |
3300 |
ANALYST |
3300 |
1925 |
2841.67 |
8525 |
HENRY |
1925 |
ANALYST |
3300 |
1925 |
2841.67 |
8525 |
FORD |
3300 |
ANALYST |
3300 |
1925 |
2841.67 |
8525 |
SMITH |
3300 |
CLERK |
3300 |
1045 |
1746.25 |
6985 |
MILLER |
1430 |
CLERK |
3300 |
1045 |
1746.25 |
6985 |
2. Using ‘Over’ clause –
- OVER CLAUSE ALONG WITH PARTITION BY IS USED TO BRAKE UP DATA INTO PARTITIONS.
- 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;
Dname |
Emp |
SALES |
6 |
RESEARCH |
5 |
ACCOUNTING |
3 |
OPERATIONS |
0 |
OTHERS |
0 |
Last Updated :
31 Aug, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...