How to exclude column from GROUP BY clause in Oracle?
To exclude column from GROUP BY clause we can use Analytical function.
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
COUNT(1) OVER (PARTITION BY A.DEPARTMENT_ID, A.JOB_ID) JOB_WISE_EMP_COUNT,
SUM(A.SALARY) OVER (PARTITION BY A.DEPARTMENT_ID) DEPT_TOTAL_SAL,
MAX(A.SALARY) OVER (PARTITION BY A.JOB_ID) JOB_MAX_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (10, 20, 30, 60)
ORDER BY A.DEPARTMENT_ID;
In the above example, we have used multiple group functions on multiple combinations of columns.
JOB_WISE_EMP_COUNT:
If we take the example of department – 30 then there are 2 different job id exists:
- PU_CLERK
- PU_MAN
There are 5 employees under job id PU_CLERK and 1 employee under job id PU_MAN
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.JOB_ID,
A.SALARY,
A.DEPARTMENT_ID,
COUNT(1) OVER (PARTITION BY A.DEPARTMENT_ID, A.JOB_ID) JOB_WISE_EMP_COUNT,
SUM(A.SALARY) OVER (PARTITION BY A.DEPARTMENT_ID) DEPT_TOTAL_SAL,
MAX(A.SALARY) OVER (PARTITION BY A.JOB_ID) JOB_MAX_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (30)
ORDER BY A.DEPARTMENT_ID;
DEPT_TOTAL_SAL:
In this column we have shown total department salary irrespective of job id:
To crosscheck the data lets extract the department total of department 10, 20, 30, 60.
SELECT A.DEPARTMENT_ID,
SUM(A.SALARY) DEPT_TOTAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (10, 20, 30, 60)
GROUP BY A.DEPARTMENT_ID;
In the original query:
JOB_MAX_SAL:
In this column we have shown the highest salary against each job id irrespective of departments.
To crosscheck the data lets extract the highest salary against each job id in department id (10, 20, 30, 60).
SELECT A.JOB_ID,
MAX(A.SALARY) JOB_MAX_SAL
FROM EMPLOYEES A
WHERE A.DEPARTMENT_ID IN (10, 20, 30, 60)
GROUP BY A.JOB_ID;
In the original query:
RELATED TOPICS:
