SORTING, GROUP BY, 통계

--ORDER BY : SORTING
 
    SELECT *
    FROM TABLE
    ORDER BY sal ASC    -> 오름차순
    ORDER BY sal DESC   -> 내림차순

NULLS FIRST

NULLS LAST -> 빈칸이 아래로가게
   

 

--오름차순
SELECT ename, sal
FROM emp
ORDER BY sal ASC;   --ASC는 생략가능

--내림차순
SELECT ename, sal
FROM emp
ORDER BY sal DESC;

 

-- alias
SELECT empno, ename, sal * 12 AS annsal
FROM emp
ORDER BY annsal DESC;

SELECT ename, comm
FROM emp
ORDER BY comm NULLS FIRST;  --NULL이 위로 옴

SELECT ename, comm
FROM emp
ORDER BY comm NULLS LAST; 

SELECT employee_id, job_id, salary
FROM employees
ORDER BY job_id ASC, salary DESC;   --JOB_ID로 SORTING 후 SALARY 역순으로 SORTING

 

 

---

 

 

--그룹으로 묶는 기능

 

DISTINT   -부서/직업

SELECT DISTINCT job_id
FROM employees
ORDER BY job_id ASC;

 

--GROUP BY      --> 그룹으로 묶은 후에는 개개인으로 출력 불가능
--HAVING : 조건절

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY department_id ASC;     -- ORDER BY는 맨 아래에 작성

 

 

---

 

 

 

*통계함수

    GROUP
    COUNT
    SUM
    AVG
    MAX
    MIN

SELECT COUNT(salary), COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary)    -- IT부서의 월급 합/평균 등 
FROM employees
WHERE job_id = 'IT_PROG';

SELECT job_id, COUNT(salary), SUM(salary), AVG(salary)      --COUNT는 인원수
FROM employees
GROUP BY JOB_ID; 

--HAVING : 반드시 GROUP BY와 같이 사용한다
SELECT job_id, SUM(salary)
FROM employees
GROUP BY job_id
HAVING SUM(salary) >= 100000;   --묶은 후 조건요청

--급여가 5000이상인 받는 사원만으로 합계를 내서 업무로 그룹화하여(JOB_ID)
--급여의 합계가 20000을 초과하는 업무명을 구하라
SELECT job_id, SUM(salary), COUNT(*)
FROM employees
WHERE salary >= 5000
    GROUP BY job_id
    HAVING SUM(salary) > 20000;