(MariaDB)SQL기본 2-GROUP BY, HAVING

SELECT문

GROUP BY

데이터들을 집계 함수를 통해 컬럼의 그룹별로 추출

예시

SUM 개수 합치기

사용자별 구매 총 수량

SELECT userID, amount 
FROM buytbl 
ORDER BY userid;

SELECT userID,SUM(amount)
FROM buytbl
GROUP BY userID
ORDER BY userid;

사용자별 총 구매액

SELECT userid, SUM(price *amount) AS '총 구매액'
FROM buytbl
GROUP BY userid
ORDER BY userid;

COUNT 행의 개수

제품별 각각의 총 수량

SELECT prodName,COUNT(prodname)
FROM buytbl
GROUP BY prodname;

AVG 평균

SELECT userid, AVG(amount) AS' 평균 구매개수'
FROM buytbl
GROUP BY userid;

HAVING절

• WHERE절과 비슷한 개념으로 조건을 제한하는 것이지만, 집계함수에 대해서 조건을 제한하는 것
• HAVING절은 꼭 GROUP BY절 다음에 나와야 함
• 기존의 데이터들로 새로운 내용으로 표현한 집계함수를 가지고 비교
• SELECT userid ,SUM(price*amount) AS '총구매액' FROM buytbl GROUP BY userid HAVING SUM(price*amount)>1000;

예제

-- Q1) 사원테이블에서 사원 이름과 월급을 구하되, 월급을 내림차순으로 출력하자.
SELECT ename,sal
FROM emp
ORDER BY sal DESC;

-- Q2) 사원테이블에서 직업별 평균 월급을 출력하되 컬럼 ALIAS를 '평균' 으로 하고, 평균 월급이 높은 순으로 정렬하자.
SELECT job,AVG(sal)AS '평균'
FROM emp
GROUP BY job ORDER BY AVG(sal) desc;

-- Q3) 사원테이블에서 직업별 총 월급을 구하고, 총 월급이 5000 이상인 것만 출력하자.
SELECT job,SUM(sal)AS '직업별 총 월급'
FROM emp
GROUP BY job
HAVING SUM(sal)>=5000;

-- Q4) 사원테이블에서 부서별 월급의 합을 구하고, 그 총합이 1000 이상인 것만 출력하자.
SELECT deptno,SUM(sal)AS'부서별 월급 합'
FROM emp
GROUP BY deptno
HAVING SUM(sal)>=1000;

-- Q1) SIZE가 10인 문자형 컬럼 ID와 PW를 가진 TEST 테이블을 생성해보자
CREATE TABLE test(ID VARCHAR(10),PW VARCHAR(10));

-- Q2) 사원 테이블(EMP)의 모든 구조와 데이터를 TEST01로 복사하여 생성해보자.
CREATE TABLE test01(SELECT * FROM emp);

-- Q3) 사원 테이블에서 사원의 번호와 이름을 TEST02로 복사하여 생성해보자.
CREATE TABLE test02(SELECT empno,ename FROM emp);

-- Q4) 사원 테이블에서 사원의 번호와 이름을 TEST03으로 복사하여 생성해보자.
-- 단, 컬럼명을 M1, M2로 변경하면서 복사하자.
CREATE TABLE test03(SELECT empno AS M1,ename AS M2 FROM emp);

-- Q5) 사원 테이블의 구조만 TEST04로 복사하여 생성해보자.
CREATE TABLE test04 LIKE emp;

CREATE TABLE test04 (SELECT * FROM emp where 1=2);

-- Q6) 부서 테이블(DEPT) 의 구조만 TEST05로 복사하여 생성해보자.
CREATE TABLE test05 LIKE dept;

CREATE TABLE test05 (SELECT * FROM dept where 1=2);

-- Q1) 사원테이블에서 평균 월급을 출력하자.
SELECT AVG(sal) AS'전체 평균 월급'
FROM emp;

-- Q2) 사원테이블에서 부서번호가 10인 부서에 근무하고 있는 사원들의 부서번호와 평균 월급을 출력하자.
SELECT deptno,AVG(sal)
FROM emp
WHERE deptno=10
GROUP BY deptno;

-- Q3) 사원테이블에서 직업이 'SALESMAN'인 사원들의 평균 월급을 출력하자.
SELECT job,AVG(sal)AS 'sales평균월급'
FROM emp
WHERE job='SALESMAN';

-- Q4) 사원테이블에서 부서별 평균 월급을 출력하자.
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;

-- Q5) 사원테이블에서 직업별 평균 월급을 출력하자.
SELECT job,AVG(sal)
FROM emp
GROUP BY job;

-- Q6) 사원 테이블에서 평균 커미션(COMM)을 출력하자.
SELECT AVG(nvl(comm,0)) AS '평균커미션'
FROM emp ;

-- Q7) 사원테이블에서 10번 부서의 최대 월급을 출력하자.
SELECT deptno,MAX(sal)
FROM emp
WHERE deptno=10;

-- Q8) 사원테이블에서 부서별 최대 월급을 출력하자.
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno;

-- Q9) 사원테이블에서 직업별 최대 월급을 출력하자.
SELECT job,MAX(sal)
FROM emp
GROUP BY job;

-- Q10) 사원테이블에서 직업이 'SALESMAN'인 사원들 중 최대월급을 출력하자.
SELECT ename,job,MAX(sal) AS 'sales최대월급'
FROM emp
WHERE job='salesman';