SQL 그룹함수

1.
SQL> select deptno, avg(weight), count(*)
from student
group by deptno
order by avg(weight) desc;

2.
SQL> select max(count(*)), min(count(*))
from student
group by grade;

3.
SQL> select deptno, grade, count(*), max(weight)
from student
group by deptno, grade
having count(*) >= 3;

4.
SQL> select deptno, grade, avg(height)
from student
group by cube(deptno, grade);

5.
SQL> select deptno, grade, avg(weight)
from student
group by rollup(deptno, grade);

6.
SQL> select deptno, count(*)
from professor
group by deptno
having count(*) <=2;

7.
SQL> select max(sal+nvl(comm,0)),
min(sal+nvl(comm,0)),
round(avg(sal+nvl(comm,0)),1)
from professor;

8.
SQL> select deptno, position, avg(sal)
from professor
group by cube(deptno, position);

9.
SQL> select position
case when avg(sal)>=300 then'우수'
else '보통'
end '구분'
from professor
group by position;