Analytical Functions | Analytical Aggregate Functions | Part 2 | #GoLearningPoint

You can find DDL and DML's from the description of Part1 Video - https://www.youtube.com/watch?v=lZW8nzTo4QQ #####Analytic aggregate functions MIN() MAX() AVG() SUM() COUNT() LISTAGG() ------------------------------------------------------------------------------------------------- SELECT EMPNO,ENAME,DEPTNO,SAL, SUM(SAL) OVER(PARTITION BY DEPTNO) AS SUM_SAL_DEPT, MAX(SAL) OVER(PARTITION BY DEPTNO) AS MAX_SAL_DEPT, MIN(SAL) OVER(PARTITION BY DEPTNO) AS MIN_SAL_DEPT, AVG(SAL) OVER(PARTITION BY DEPTNO) AS AVG_SAL_DEPT, COUNT(DISTINCT JOB) OVER(PARTITION BY DEPTNO) AS DISTINCT_JOB_DEPT_WISE, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS WIN_FRAME1, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS WIN_FRAME2, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS WIN_FRAME3 FROM EMPLOYEE; ------------------------------------------------------------------------------------------------- SELECT JOB, LISTAGG(ENAME,';') WITHIN GROUP(ORDER BY JOB) AS EMP_LIST FROM EMPLOYEE GROUP BY JOB; SELECT DEPTNO, LISTAGG(ADDRESS,'|') WITHIN GROUP(ORDER BY DEPTNO) AS ADDRESS_LIST FROM ( SELECT DISTINCT DEPTNO,ADDRESS FROM EMPLOYEE ) GROUP BY DEPTNO; ------------------------------------------------------------------------------------------------- Playlist Link - https://www.youtube.com/watch?v=5O1VhB_B5gg&list=PLEqdEgiHtu2JanXoipoDvASOHDK5LJrRT #sql #golearningpoint #database #dataengineering #oracle