DB 5일차 2(group by)

SELECT	
	
	COUNT(order_id), /*행의 개수가 몇개인가를 알려줌?*/
	SUM(stock),
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	

	
GROUP BY
	product_name
	
HAVING
	max_stock >49;

SELECT
	product_name,
	price * stock AS total_price 
FROM
	order_mst;

각 행안에서 가격과 주문량을 곱한다.

 

SELECT
	product_name,
	SUM(price * stock) AS total_price /*상품가격에 대한 총합?.*/
FROM
	order_mst
GROUP BY
	product_name;

상품기준으로 발생한 총 매출

 

 

SELECT	
	
	COUNT(order_id), 
	SUM(stock),
	MIN(stock),
	MAX(stock),
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
GROUP BY
	product_name;

 

 

SELECT	
	
	COUNT(order_id), /*행의 개수가 몇개인가를 알려줌?*/
	SUM(stock),
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
GROUP BY
	product_name
	
HAVING
	max_stock >99;

max stock 이 99 보다 큰것

 

SELECT	
	
	COUNT(order_id), /*행의 개수가 몇개인가를 알려줌?*/
	SUM(stock),
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	

	
GROUP BY
	product_name
	
HAVING
	max_stock >49;

 

SELECT	
	
	COUNT(order_id), /*행의 개수가 몇개인가를 알려줌?*/
	SUM(stock),
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
where
	stock >50
GROUP BY
	product_name
	
HAVING
	max_stock >49;

 

SELECT	
	product_name,
	COUNT(order_id) AS order_count, 
	SUM(stock),
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
where
	stock >50
GROUP BY
	product_name
	
HAVING
	max_stock >49
	
ORDER BY
	order_count
	;

 

SELECT	
	product_name,
	COUNT(order_id) AS order_count, 
	SUM(stock) AS stock_sum,
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
where
	stock >50
GROUP BY
	product_name
	
HAVING
	max_stock > 49
	
ORDER BY
	order_count,
	stock_sum DESC;

 

SELECT	
	product_name,
	COUNT(order_id) AS order_count, 
	SUM(stock) AS stock_sum,
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
where
	stock >50
GROUP BY
	product_name
	
HAVING
	max_stock > 49
	
ORDER BY
	order_count desc,
	stock_sum ;

 

리미트 페이징 처리할 때 쓰인다

SELECT	
	product_name,
	COUNT(order_id) AS order_count, 
	SUM(stock) AS stock_sum,
	MIN(stock),
	MAX(stock) AS max_stock,
	SUM(stock) /COUNT(1),
	AVG(stock)

FROM
	order_mst
	
where
	stock >50
GROUP BY
	product_name
	
HAVING
	max_stock > 49
	
ORDER BY
	order_count desc,
	stock_sum desc
	
LIMIT 0,2;

 

SELECT
	*
FROM
	order_mst
LIMIT 3,3;