| ☰ See All Chapters |
Oracle GROUP BY Clause
The Oracle GROUP BY Clause is used to in association with the SELECT statement to arrange identical data into groups by one or more column.
GROUP BY clause also orders the results by the specified column, means GROUP BY clause groups and orders the results. So unless you need to order the values in a different pattern than the GROUP BY clause, the ORDER BY clause is redundant.
Always aggregate functions will be applied on groups, means GROUP BY clause uses aggregate functions AVG, MAX, MIN, SUM, and COUNT to arrange data in groups.
All columns and expressions in the SELECT statement must be listed in the GROUP BY clause, with the exception for aggregate columns (those columns targeted by an aggregate function). Columns selected for GROUP BY clause must be in SELECT clause. If a column is not in the SELECT statement, you cannot use it in the GROUP BY clause. When grouping the data, the order of columns grouped does not have to match the column order in the SELECT clause.
The position of the GROUP BY clause in a query is as follows:
SELECT FROM WHERE GROUP BY ORDER BY |
Oracle GROUP BY Clause syntax
SELECT COLUMN1, COLUMN2 FROM TABLE WHERE <Condition> GROUP BY COLUMN1, COLUMN2 |
Like the ORDER BY clause, you can order the GROUP BY clause by using an integer to represent the column name.
SELECT COLUMN1, COLUMN2 FROM TABLE WHERE <Condition> GROUP BY 1, 2 |
Following steps happens for above query
Order the entire TABLE considering COLUMN1 (Ascending order, 0-9 for numeric, A-Z for alphabets). While ordering if same data is found in COLUMN1 then pre-existing order of TABLE is used for same data.
If same data is present in COLUMN1 (column of GROUP BY clause) group them, which mean do partition the TABLE where each partition will be having same data in all rows of COLUMN1 in their own partition.
Now perform SELECT COLUMN1, COLUMN2 on partitions separately. This select statement will return only one row for each partition, which will be the first row of each partition. If COLUMN1 or COLUMN2 involves aggregate functions then the row which results from aggregate function will be given.
Oracle GROUP BY Clause examples
Creating table for demonstrating GROUP BY clause
CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2));
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300);
COMMIT; |
Example 1
SELECT JOB, COUNT(*),ENAME FROM EMP GROUP BY JOB, ENAME |
Example 2
SELECT ENAME,EMPNO,COUNT(*) FROM EMP GROUP BY EMPNO, ENAME |
Example 3
SELECT SUM(SAL),JOB FROM EMP GROUP BY JOB |
Example 4
SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB |
All Chapters
