View 활용해보기

연도별 각 고객의 매출 금액 구하기 뷰 생성

CREATE OR REPLACE VIEW customer_sales AS
SELECT
	c.name AS customer,
	TO_CHAR(a.order_date, 'YYYY') AS YEAR,
	SUM(b.quantity * b.unit_price) sales_amount
	FROM ORDERS A,
	ORDER_ITEMS B,
	customers C
WHERE 1=1
	AND a.STATUS = 'Shipped'
	AND a.ORDER_ID = b.ORDER_ID 
	AND a.CUSTOMER_ID = c.CUSTOMER_ID 
GROUP BY c.NAME, TO_CHAR(a.ORDER_DATE, 'YYYY') 
ORDER BY c.NAME;


SELECT * FROM customer_sales;

결과

고객별 2017년도 매출 구하기

SELECT 
	customer,
	sales_amount
FROM 
	customer_sales
WHERE 
	YEAR = 2017
ORDER BY 
	sales_amount DESC;

결과

고객별 전체 매출 합계 구하기

SELECT 
	customer,
	sum(sales_amounts) AS sum_sales_amount
FROM customer_sales 
GROUP BY CUSTOMER 
ORDER BY sum_sales_amount DESC;

결과