Group by clause using group functions and having

syntax sql> select t1.col, group_function
from table t1
where condition
group by t1.col
having condition on group_function;

SQL> select * from emp;

   ENO ENAME             SAL JOB        DOJ          DEPT_ID DAY

---

   115 CHANDU          25000 manager    10-MAR-12        222 saturday
   105 kumar           12200                             555
   111 RAM             12000 A          12-DEC-14        222 friday
   112 RAMESH          25000 B          29-DEC-14        333 monday
   113 KRISHNA         30000 C          03-JAN-14        555 friday
   114 SHEKAR          56000 manager    01-JAN-14        555 wednesday

6 rows selected.

SQL> select dept_id, sum(sal)
2 from emp
3 group by dept_id;

DEPT_ID SUM(SAL)

---

   555      98200
   222      37000
   333      25000

SQL> select dept_id,count(eno)
2 from emp
3 group by dept_id;

DEPT_ID COUNT(ENO)

---

   555          3
   222          2
   333          1

SQL> select d.dept_id, d.dept_name, count(*)
from dept d, emp e
where d.dept_id = e.dept_id
group by d.dept_id, d.dept_name;

DEPT_ID DEPT_NAME COUNT(ENO)

---

   555         IT         3
   222         SALES     2
   333         FINANCE     1

using having:

SQL> select dept_id,count(eno)
2 from emp
3 group by dept_id
4 having count(eno)>1;

DEPT_ID COUNT(ENO)

---

   555          3
   222          2

where and having in group by clause

SQL> select dept_id,count(eno)
2 from emp
3 where sal>20000
4 group by dept_id
5 having count(eno)>1;

DEPT_ID COUNT(ENO)

---

   555          2

Share this post: on Twitter on Facebook on Google+