23.Group by Clause

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>

Share this post: on Twitter on Facebook on Google+