[SQL] GROUP BY 절 예제

Q. 소속 부서별 사원의 수와 커미션을 받는 사원의 수를 카운트 하시오(커미션0도 카운트한다.) **

   select deptno, count(*), count(comm) 
   from emp group by deptno;

Q. emp 테이블에서 년도 별로 사원들이 몇명 입사했는지 카운트 하시오

   select SUBSTR(hiredate,1,2) "년도", count(*) 
   from emp group by SUBSTR(hiredate, 1,2);

   select to_char(hiredate, 'YYYY'), count(*) 
   from emp group by to_char(hiredate, 'YYYY');

   select count(decode(to_char(hiredate,'YYYY'), '1980', 1)) as "1980",
   		  count(decode(to_char(hiredate,'YYYY'), '1981', 1)) as "1981",
		  count(decode(to_char(hiredate,'YYYY'), '1982', 1)) as "1982",
		  count(decode(to_char(hiredate,'YYYY'), '1987', 1)) as "1987"
   from emp;

Q. emp 테이블에서 월별로 사원들이 몇 명 입사했는지 카운트하시오.

   select SUBSTR(hirdate, 4,2) "월별" , count(*) 
   from emp group by substr(hiredate, 4,2);

   select to_char(hiredate, 'MM'), count(*) 
   from emp group by to_char(hiredate, 'MM');

   select count(decode(to_char(hiredate,'MM'), '01', 1)) as "01",
   		  count(decode(to_char(hiredate,'MM'), '02', 1)) as "02",
		  count(decode(to_char(hiredate,'MM'), '03', 1)) as "03",
		  count(decode(to_char(hiredate,'MM'), '04', 1)) as "04",
		  count(decode(to_char(hiredate,'MM'), '05', 1)) as "05",
		  count(decode(to_char(hiredate,'MM'), '06', 1)) as "06",
		  count(decode(to_char(hiredate,'MM'), '07', 1)) as "07",
		  count(decode(to_char(hiredate,'MM'), '08', 1)) as "08",
		  count(decode(to_char(hiredate,'MM'), '09', 1)) as "09",
		  count(decode(to_char(hiredate,'MM'), '10', 1)) as "10",
		  count(decode(to_char(hiredate,'MM'), '11', 1)) as "11",
		  count(decode(to_char(hiredate,'MM'), '12', 1)) as "12"		
   from emp;

=> 문자형으로 바꾼 입사년도를 1980년과 비교를 하여서 true 이면 1의 값을 주었다.
	      (decode 1번식, '1980, 1)
		  1의 값을 준 (1980년도)것을 count 하였다.