Hi,
I will explain GROUP BY Statement in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
IN and BETWEEN Operator in Oracle SQL | Oracle SQL Tutorials -15
GROUP BY Statement
GROUP BY statement is very popular in Oracle SQL. You can group specific or all rows which have same values into summary rows.
Aggregate functions (MAX, MIN, SUM, AVG,COUNT) are often used in the Group by statements.
GROUP BY Syntax
GROUP BY syntax is as follows.
SELECT column1,column2 FROM table_name WHERE condition GROUP BY column1,column2;
SELECT column1,column2 FROM table_name WHERE condition GROUP BY column1,column2
ORDER BY column_name(s);
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
Group by examples are as follows.
SQL> select first_name,last_name,salary from hr.employees where department_id in (20,30,40) group by first_name,last_name,salary;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Alexander Khoo 4100
Pat Fay 7000
Karen Colmenares 3500
Michael Hartstein 14000
Guy Himuro 3600
Den Raphaely 12000
Shelli Baida 3900
Sigal Tobias 3800
Susan Mavris 7500
9 rows selected.
SQL> select salary,count(*) from hr.employees where department_id in (20,30,40,50,60,70) group by salary;
SALARY COUNT(*)
---------- ----------
5200 2
12000 1
9000 1
4000 2
5000 1
14000 1
11000 1
10000 1
3900 3
3400 2
4600 2
SALARY COUNT(*)
---------- ----------
4500 1
3800 4
3500 6
4100 4
4400 1
4800 1
8900 1
7500 2
4200 4
4300 2
4900 1
SALARY COUNT(*)
---------- ----------
3600 4
3700 2
5800 2
6800 1
3200 2
3100 1
5100 1
7000 2
9200 1
31 rows selected.
SQL>
SQL> select department_id,sum(salary) from hr.employees group by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
100 57608
30 30900
8000
90 46000
20 21000
70 11000
110 22308
50 201400
80 338500
40 7500
60 33800
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 5400
12 rows selected.
SQL>
Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course