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