Oracle SQL Aggregate Functions

COUNT, SUM, AVG, MAX, MIN 등의 집계 함수 활용 (Using Aggregate Functions like COUNT, SUM, AVG, MAX, MIN)

Oracle SQL에서는 데이터를 요약하고 분석하기 위해 다양한 집계 함수를 제공합니다. 주요 함수들은 다음과 같습니다:

COUNT

특정 열 또는 행의 개수를 반환합니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT COUNT(column_name)
FROM table_name;
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(column_name) 
FROM table_name;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT COUNT(employee_id)
FROM employees;
SELECT COUNT(employee_id) FROM employees;
SELECT COUNT(employee_id) 
FROM employees;

SUM

특정 열의 합계를 계산합니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT SUM(column_name)
FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT SUM(column_name) 
FROM table_name;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT SUM(salary)
FROM employees;
SELECT SUM(salary) FROM employees;
SELECT SUM(salary) 
FROM employees;

AVG

특정 열의 평균 값을 계산합니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT AVG(column_name)
FROM table_name;
SELECT AVG(column_name) FROM table_name;
SELECT AVG(column_name) 
FROM table_name;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT AVG(salary)
FROM employees;
SELECT AVG(salary) FROM employees;
SELECT AVG(salary) 
FROM employees;

MAX

특정 열의 최대 값을 반환합니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT MAX(column_name)
FROM table_name;
SELECT MAX(column_name) FROM table_name;
SELECT MAX(column_name) 
FROM table_name;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT MAX(salary)
FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MAX(salary) 
FROM employees;

MIN

특정 열의 최소 값을 반환합니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT MIN(column_name)
FROM table_name;
SELECT MIN(column_name) FROM table_name;
SELECT MIN(column_name) 
FROM table_name;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT MIN(salary)
FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MIN(salary) 
FROM employees;

그룹화 (GROUP BY) (Grouping Data with GROUP BY)

GROUP BY 절을 사용하면 데이터를 하나 이상의 열을 기준으로 그룹화할 수 있습니다. 그룹화된 데이터는 집계 함수와 함께 사용됩니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;
SELECT column1, aggregate_function(column2) 
FROM table_name 
GROUP BY column1;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

위 예제에서는 department_id를 기준으로 그룹화하여 각 부서의 평균 급여를 계산합니다.

그룹화된 데이터 필터링 (HAVING 절) (Filtering Grouped Data with HAVING Clause)

HAVING 절을 사용하면 그룹화된 데이터에 대해 조건을 적용할 수 있습니다. HAVING 절은 GROUP BY 절 다음에 사용되며, 집계 함수의 결과를 기준으로 필터링할 수 있습니다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;
SELECT column1, aggregate_function(column2) 
FROM table_name 
GROUP BY column1 
HAVING condition;

예제:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000;
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) > 50000;

위 예제에서는 department_id를 기준으로 그룹화하고, 각 부서의 평균 급여가 50,000 이상인 부서만 조회합니다.

예제 종합 (Comprehensive Examples)

종합적으로 집계 함수, 그룹화, 그룹화된 데이터 필터링을 사용하는 예제입니다:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT job_id, COUNT(employee_id) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary, MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM employees
GROUP BY job_id
HAVING AVG(salary) > 60000
ORDER BY average_salary DESC;
SELECT job_id, COUNT(employee_id) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary, MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees GROUP BY job_id HAVING AVG(salary) > 60000 ORDER BY average_salary DESC;
SELECT job_id, COUNT(employee_id) AS employee_count, SUM(salary) AS total_salary, AVG(salary) AS average_salary, MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary 
FROM employees 
GROUP BY job_id 
HAVING AVG(salary) > 60000 
ORDER BY average_salary DESC;

이 예제에서는 각 직무(job_id)를 기준으로 다음을 계산합니다:

  • 직원 수 (COUNT(employee_id))
  • 총 급여 (SUM(salary))
  • 평균 급여 (AVG(salary))
  • 최고 급여 (MAX(salary))
  • 최저 급여 (MIN(salary))

또한 평균 급여가 60,000 이상인 직무만 필터링하고, 평균 급여를 기준으로 내림차순 정렬합니다.

Oracle SQL의 집계 함수, GROUP BY 절 및 HAVING 절을 활용하여 데이터를 효과적으로 요약하고 분석할 수 있습니다. 이를 통해 다양한 비즈니스 인사이트를 도출할 수 있습니다.

Leave a Reply

Your email address will not be published. Required fields are marked *