[ SQLD ] Oracle SQL (2) - 집계 함수 [count, max, min, sum, avg]

 

Oracle Documentation

- 이번 포스팅에서는 ORACLE SQL의 GROUP BY에 대해서 알아보자.

 

오라클에서 제공하는 SQL 공식 문서이다. 아래를 읽으면서 알아보자.

Aggregate Functions

---

 

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups.

 

In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

 

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

 

 

Many (but not all) aggregate functions that take a single argument accept these clauses:

• DISTINCT and UNIQUE, which are synonymous, cause an aggregate function to consider only distinct values of the argument expression. The syntax diagrams for aggregate functions in this chapter use the keyword DISTINCT for simplicity.

• ALL causes an aggregate function to consider all values, including all duplicates.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.

Some aggregate functions allow the windowing_clause, which is part of the syntax of analytic functions. Refer to windowing_clause for information about this clause. In the listing of aggregate functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*)

All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT and REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

The aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV, when followed by the KEEP keyword, can be used in conjunction with the FIRST or LAST function to operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. Refer to FIRST for more information.

You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr:

 

---

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

 

Oracle SQL 예제 (2) - Group By

 

1. [SUM, GROUP BY] 사원 테이블에서 부서별 봉급의 합계를 구하자.

    DEPTNO   SUM(SAL)

---------- ----------

        30       9400

        20       6775

        10       8750

SELECT DEPTNO, SUM(SAL)

FROM EMP

GROUP BY DEPTNO;

 

 

2. [AVG, GROUP BY] 사원 부서별 평균 월급을 구해보자.

    DEPTNO   AVG(SAL)

---------- ----------

        30 1566.66667

        20 2258.33333

        10 2916.66667

SQL> SELECT DEPTNO, AVG(SAL)

  2  FROM EMP

  3  GROUP BY DEPTNO;

 

 

3. [AVG, GROUP BY] 직업별 평균 월급을 구해보자.

    DEPTNO   AVG(SAL)

---------- ----------

        30 1566.66667

        20 2258.33333

        10 2916.66667

SQL> SELECT DEPTNO, AVG(SAL)

  2  FROM EMP

  3  GROUP BY DEPTNO;

 

4. [AVG] 10번 부서의 평균 월급을 구하자

    DEPTNO   AVG(SAL)

---------- ----------

        10 2916.66667

 

SQL> SELECT DEPTNO, AVG(SAL)

  2  FROM EMP

  3  WHERE DEPTNO = 10

  4  GROUP BY DEPTNO;

 

5. [MAX] 직위가 'SALESMAN'인 사원들 중 최대 월급을 출력하라.

  MAX(SAL)

----------

      1600

 SQL> SELECT JOB, MAX(SAL) 

  2  FROM EMP

  3  WHERE JOB = 'SALESMAN';
  
  
// 집계 함수가 있다면 반드시 GROUP BY 가 있어야 한다.

 

6. [SUM] 부서별 커미션(COMM)의 합계를 구해보자.

    DEPTNO  SUM(COMM)

---------- ----------

        30       2200

        20

        10

SQL> SELECT DEPTNO, SUM(COMM) FROM EMP

  2  GROUP BY DEPTNO;

 

7. [AVG] 부서별 커미션(COMM)의 평균를 구해보자***

AVG(NVL(COMM,0))

----------------

      183.333333

SQL> SELECT AVG(NVL(COMM,0)) FROM EMP; 



// NULL을 0으로 바꿔줘야, 평균을 구할 때 인원 수만큼 제대로 나눌 수 있기 때문.

 

 

8. [ROLLUP]과 [CUBE] : 총합계 표시하는 명령어

 

ROLLUP

     DEPTNO   SUM(SAL)

---------- ----------

        10       8750

        20       6775

        30       9400

                24925

SQL> SELECT DEPTNO, SUM(SAL)

  2  FROM EMP

  3  GROUP BY ROLLUP(DEPTNO);

CUBE

    DEPTNO   SUM(SAL)

---------- ----------

                24925

        10       8750

        20       6775

        30       9400

SQL> SELECT DEPTNO, SUM(SAL)

  2  FROM EMP

  3  GROUP BY CUBE(DEPTNO);