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
Table – DEPARTMENT TABLE
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:
- 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 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:
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
- Using ‘Over’ clause –
- OVER CLAUSE ALONG WITH PARTION BY IS USED TO BRAKE UP DATA INTO PARTITIONS.
- 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;
Dname Emp SALES 6 RESEARCH 5 ACCOUNTING 3 OPERATIONS 0 OTHERS 0My Personal Notes arrow_drop_up
- SQL | Join (Inner, Left, Right and Full Joins)
- Difference between Having clause and Group by clause
- Joins in MS SQL Server
- SQL | Functions (Aggregate and Scalar Functions)
- SQL query using COUNT and HAVING clause
- SQL | USING Clause
- Difference between Where and Having Clause in SQL
- Difference between order by and group by clause in SQL
- Difference between From and Where Clause in SQL
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | SELECT TOP Clause
- SQL | Union Clause
- SQL | WITH clause
- SQL | Except Clause
- SQL | OFFSET-FETCH Clause
- Having vs Where Clause in SQL
- SQL | LIMIT Clause
- SQL | Intersect & Except clause
- SQL | With Ties Clause
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.