Group by Clause : Grouping the repeated values in a column of the table and show as unique value
================
Syntax: : select t1.col1,
t1.col2,
t2.col1,
t2.col2…..
FROM table1 t1,
table2 t2
WHERE
GROUP BY t1.col1,t1.col2, t2.col1, t2.col2…. (all selected columns)
HAVING
Points to remember : 1. All the selected columns should be specified in the group by clause with same sequence
2. If more than one column is selected, then the combination of values should be repeated,
then it shows as the unique combination
3. Where condition should be always before the group by clause
4. Having condition should be always after the group by clause
SAmple Data:
SQL> select * from dept;
DEPT_ID DEPT_NAME
222 hr
333 finnaces
444 markting
555 manager
SQL> select * from emp_new;
ENO ENAME SAL JOB DOJ DEPT_ID
115 CHANDU 25500 manager 10-MAR-12 222
105 kumar 12200 555
111 RAM 12000 manager 12-DEC-14 222
112 RAMESH 25000 B 29-DEC-14 333
113 KRISHNA 30000 C 03-JAN-14 555
114 SHEKAR 56500 manager 01-JAN-14 555
Example:
Show dept_id without group by clause
SQL> select dept_id
2 from emp_new;
DEPT_ID
222
555
222
333
555
555
6 rows selected.
Using Group by clause
SQL> SELECT e.dept_id
2 FROM emp_new e
3 GROUP BY e.dept_id;
DEPT_ID
555
222
333
Example: Grouping more than one column
Without Group
SQL> select e.job,
e.dept_id
from emp_new e;
JOB DEPT_ID
manager 222
manager 222
B 333
C 555
manager 555
Using Group by clause
SQL> select e.job,
e.dept_id
from emp_new e
group by e.job,
e.dept_id;
JOB DEPT_ID
555
manager 222
C 555
manager 555
B 333
Using more than one table and where condition
SQL> SELECT d.dept_id,
d.dept_name,
e.job
from dept d,
emp_new e
WHERE d.dept_id = e.dept_id
SQL> /
DEPT_ID DEPT_NAME JOB
222 hr manager
555 manager
222 hr manager
333 finnaces B
555 manager C
555 manager manager
6 rows selected.
Using group by clause
SQL> SELECT d.dept_id,
d.dept_name,
e.job
from dept d,
emp_new e
WHERE d.dept_id = e.dept_id
GROUP BY d.dept_id,
d.dept_name,
e.job;
DEPT_ID DEPT_NAME JOB
555 manager C
222 hr manager
333 finnaces B
555 manager
555 manager manager
SQL>