[SQL Server] Functions - 집계 함수

집계 함수

허용 범위

• SELECT 문
• HAVING 절

※ COUNT(*) 를 제외한 집계 함수는 NULL 값을 무시함

---

AVG

AVG ( [ ALL | DISTINCT ] expression )  
   [ OVER ( [ partition_by_clause ] order_by_clause ) ]

---

COUNT / COUNT_BIG

-- COUNT > 반환 값이 INT
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )  
COUNT ( [ ALL ]  { expression | * } ) OVER ( [ <partition_by_clause> ] )

-- COUNT_BIG > 반환 값이 BIGINT
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )  
COUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] )

---

GROUPING / GROUPING_ID

-- GROUPING > GROUP BY 목록이 집계되었는지 여부
GROUPING ( <column_expression> )

-- GROUPING_ID > 그룹 수준을 계산
GROUPING_ID ( <column_expression>[ ,...n ] )

---

MAX

MAX( [ ALL | DISTINCT ] expression )  
MAX ([ ALL ] expression) OVER ( <partition_by_clause> [ <order_by_clause> ] )

---

MIN

MIN ( [ ALL | DISTINCT ] expression )  
MIN ( [ ALL ] expression ) OVER ( [ <partition_by_clause> ] [ <order_by_clause> ] )

---

STDEV / STDEVP

-- STDEV > 모든 값에 대한 통계적 표준 편차
STDEV ( [ ALL | DISTINCT ] expression )   
STDEV ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

-- STDEVP > 모집단에 대한 통계적 표준 편차
STDEVP ( [ ALL | DISTINCT ] expression )  
STDEVP ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

---

STRING_AGG

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

---

SUM

SUM ( [ ALL | DISTINCT ] expression )  
SUM ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

---

VAR / VARP

-- VAR > 모든 값의 통계적 분산
VAR ( [ ALL | DISTINCT ] expression )  
VAR ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

-- VARP > 모든 값의 모집단에 대한 통계적 분산 
VARP ( [ ALL | DISTINCT ] expression )  
VARP ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

---