--AVG(): NULL 값이 있는 경우 계산에서 제외된다
--Products 테이블에 있는 모든제품의 가격평균
SELECT AVG(prod_price) AS avg_price
FROM products;
--DLL01의 공급업체에 대한 제품 평균
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
--COUNT()
--COUNT(*): 테이블의 모든 행의 개수, NULL값 포함
--COUNT(컬럼): 컬럼의 NULL값을 제외한 행의 개수
--Customers 테이블에 있는 모든 고객의 수
SELECT COUNT(*) as num_cust
FROM customers;
--cust_email 열에 값이 있는 고객의 수만 계산
SELECT COUNT(cust_email) as num_cust
FROM customers;
--MAX(): 지정한 열에서 가장 큰 값을 반환
--TEXT도 사용가능, NULL값은 무시된다
--Products 테이블에서 가격이 가장 비싼 제품의 가격
SELECT MAX(prod_price) AS max_price
FROM products;
--MIN(): 지정한 열에서 가장 낮은 값을 반환
--TEXT도 사용가능, NULL값은 무시된다
--Products 테이블에서 가격이 가장 저렴한 제품의 가격
SELECT MIN(prod_price) AS min_price
FROM products;
--SUM(): 지정한 열에서 모든 값을 더한 합계
--NULL값은 무시된다
--모든 주문한 물품의 수량의 합계
SELECT SUM(quantity) AS items_ordered
FROM orderitems;
--각 주문에 대한 총 금액을 반환
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
--고유값의 집계: SUM, MAX, MIN에서 사용
--가격이 같은 물품이 있는 경우 한번만 계산에 포함
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
--집계함수 결합
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products;
--1. 고객주문에서 주문날자가 가장 최근인 날자와 가장 최초인 날자를 추출하시오
SELECT TO_CHAR(MAX(order_date), 'YYYY-MM-DD'), TO_CHAR(MIN(order_date), 'YYYY-MM-DD')
FROM orders;
--2. 주문상품에서 제품번호가 BN으로 시작하는 각 주문의 총금액(항목수량*항목가격)이 가장큰 금액을 추출하시오
SELECT MAX(quantity*item_price)
FROM orderitems
WHERE prod_id LIKE 'BN%';
--3. 주문상품에서 제품번호가 BR01와 BR03인 제품의 주문된 항목수량의 평균을 추출하시오
SELECT AVG(quantity)
FROM orderitems
WHERE prod_id IN ('BR01', 'BR03');
데이터 그룹화
--그룹만들기
--중첩된 그룹이 있을 경우 데이터는 마지막 지정된 그룹을 기준으로 요약된다
--GROUP BY로 그룹화 되면 해당 컬럼은 SELECT절에 나타나야 한다
--NULL값도 그룹으로 분류된다
--공급업체별 상품수
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
--필터링 그룹: 그룹을 만든다음 그 그룹결과에서 그룹함수를 통해 필터링을 수행
--WHERE: 그룹화 하기 전에 필터링 수행
--HAVING: 그룹화 한 후에 필터링 수행
--주문한 고객ID별 주문수
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
--가격이 4이상인 제품을 두 개 이상 가진 공급업체를 추출
SELECT vend_id, COUNT(*) AS products
FROM products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
--ORDER BY와 함께 사용
--주문번호별 제품수
SELECT order_num, COUNT(*) AS items
FROM orderitems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
--주문된 제품의 종류수
SELECT COUNT(DISTINCT prod_id)
FROM orderitems;
--주문된 제품별 총 주문수량
SELECT prod_id, SUM(quantity)
FROM orderitems
GROUP BY prod_id;
--주문된 제품의 총 주문수량이 100개가 넘은 제품의 제품번호
SELECT prod_id, SUM(quantity)
FROM orderitems
GROUP BY prod_id
HAVING SUM(quantity) > 100;
-- 주문번호가 20005, 20007인 주문된 제품중에 총 주문수량이 100개가 넘은 제품의 제품번호
SELECT prod_id, SUM(quantity)
FROM orderitems
WHERE order_num IN(20005, 20007)
GROUP BY prod_id
HAVING SUM(quantity) > 100;
--1. 주문제품의 주문번호와 주문번호별 주문제품의 수를 추출하시오
--결과: 주문번호, 주문제품의수
SELECT order_num, COUNT(prod_id)
FROM orderitems
GROUP BY order_num;
--2. 주문에서 주문날자별 주문한 고객의 수를 추출하시오
--결과: 주문날자(YYYY-MM-DD), 고객의 수
SELECT TO_CHAR(order_date, 'YYYY-MM-DD'), COUNT(DISTINCT cust_id)
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM-DD');
--3. 제품에서 공급업체번호별 제품의 수를 추출하시오
--결과: 공급업체번호, 제품의수
SELECT vend_id, COUNT(prod_id)
FROM products
GROUP BY vend_id;
--4. 고객 중에 우편번호가 4로 시작되는 고객의 수를 추출하시오
--결과: 고객의 수
SELECT COUNT(cust_id)
FROM customers
WHERE cust_zip LIKE '4%';
--5. 고객 중에 이메일 주소가 없는 고객의 수를 추출하시오
--결과: 고객의 수
SELECT COUNT(cust_id)
FROM customers
WHERE cust_email IS NULL;
--6. 제품중에 공급업체별 제품가격의 평균을 추출하시오
--결과: 공급업체번호, 평균가격(소수점1자리)
SELECT vend_id, AVG(prod_price)
FROM products
GROUP BY vend_id;
--7. 고객에서 주별 고객의 수를 추출하시오
--결과: 고객주, 고객의수
SELECT cust_state, COUNT(cust_id)
FROM customers
GROUP BY cust_state;
--8. 제품에서 공급업체가 BRS01, DLL01인 제품중에 가장비싼 제품의 가격이 5$이상인 제품의 공급업체번호를 추출하시오
--결과: 공급업체번호
SELECT vend_id
FROM products
WHERE vend_id IN('BRS01', 'DLL01')
GROUP BY vend_id
HAVING MAX(prod_price) >= 5;
--9. 주문에서 1월중 주문한 주문중에 고객별 가장늦게 주문한 주문일자를 추출
--결과: 고객번호, 주문일자(YYYY-MM-DD)
SELECT cust_id, MAX(TO_CHAR(order_date, 'YYYY_MM_DD'))
FROM orders
WHERE TO_CHAR(order_date, 'MM') = '01'
GROUP BY cust_id;
