집계함수

집계함수

MIN, MAX, SUM, AVG, COUNT

 

-- 전체 데이터를 집계해서 한줄로 표현
SELECT COUNT(*),
       SUM(HEIGHT),
       AVG(HEIGHT),
       MAX(HEIGHT),
       MIN(HEIGHT)
FROM ARTIST;

---

 

 -- NULL을 포함한 중복제거
SELECT DISTINCT  GROUP_NAME FROM ARTIST;

SELECT COUNT(*), -- NULL 포함 전체 개수
        COUNT(GROUP_NAME), -- NULL 포함X
        COUNT(DISTINCT GROUP_NAME) -- NULL 포함X
FROM ARTIST;

SELECT COUNT(*) AS 전체행수,
       COUNT(FOOT_SIZE) AS NULL제외행수
FROM ARTIST;

 

---

출제포인트1.

1) NULL 처리

SELECT COUNT(*) AS 전체행수,
       COUNT(FOOT_SIZE) AS NULL제외행수,
       SUM(FOOT_SIZE),
       AVG(FOOT_SIZE),
       MAX(FOOT_SIZE),
       MIN(FOOT_SIZE)
FROM ARTIST
WHERE FOOT_SIZE IS NULL;

 

2) 공집합일때

SELECT COUNT(*) AS 전체행수,
       COUNT(FOOT_SIZE) AS NULL제외행수,
       SUM(FOOT_SIZE),
       AVG(FOOT_SIZE),
       MAX(FOOT_SIZE),
       MIN(FOOT_SIZE)
FROM ARTIST
WHERE 1=2; -- false

 

 

---

https://inf.run/ZP5qg

8시간 완성 SQLD(2과목) 강의 | 땃지 - 인프런