[Mssql] 집계함수 - COUNT, DISTINCT, SUM, AVG, MIN, MAX

COUNT

NULL 데이터가들어가 있으면 무시한고 집계한다. 집계함수에서 (*)할 수 있는 유일한 함수

SELECT COUNT(*) AS PLAYERCOUNT
FROM PLAYERS;

DISTINCT(중복제거)

SELECT DISTINCT birthCity
FROM PLAYERS;

 

DISTINCT와 COUNT 함께사용 할 때 아래와 같이 사용하면 COUNT가 우선 순위기 때문에 DISTINCT 적용 안됨.

SELECT DISTINCT COUNT(birthCity)
FROM PLAYERS;

아래와 같이 해결 해야 된다.

SELECT COUNT(DISTINCT birthCity)
FROM PLAYERS;

AVG

SELECT AVG(CASE WHEN weight IS NULL THEN 0 ELSE weight END)
FROM PLAYERS;

SUM

SELECT SUM(weight)
FROM PLAYERS;

MAX

SELECT MAX(weight)
FROM PLAYERS;

MIN

SELECT MIN(weight)
FROM PLAYERS;