[TIL] 엑셀보다 쉽고 빠른 SQL _2주차_group by, order by

sum 값 구하기

select food_preparation_time,
       delivery_time,
       food_preparation_time + delivery_time as total_time
from food_orders

평균 값 구하기

select sum(food_preparation_time) total_food_preparation_time,
       avg(delivery_time) avg_delivery_time
from food_orders

select AVG(age) as avg_age 
from customers 

총 개수 구하기 / 중복값 제외한 개수 구하기

select count(1) count_of_orders,
       count(distinct customer_id) count_of_customers
from food_orders

select count(1) as total_count, 
	   count(distinct pay_type) count_of_pay_type
from payments 

최대값 / 최소값 구하기

select min(price) min_price,
       max(price) max_price
from food_orders 

where 절과 함께 쓰기

elect count(1) count_of_orders
from food_orders 
where price >= 30000

select AVG(price) as avg_price 
from food_orders 
where cuisine_type ='Korean'

order by

=> 액셀에서 정렬과도 같음
1) 평균가격 순으로 정렬 / 내림차순의 경우 desc

select cuisine_type,
       sum(price) sum_of_price
from food_orders
group by cuisine_type 
order by sum_of_price

select restaurant_name, MAX(price) as max_price 
from food_orders
group by restaurant_name 
order by max_price desc

select pay_type , max(`date`) as recent_date
from payments 
group by pay_type

2) 가격 총합으로 정렬

select cuisine_type,
       sum(price) sum_of_price
from food_orders
group by cuisine_type
order by sum(price) DESC 

숙제

Q : 음식 종류별 가격의 최대값, 최소값, 가격별로 정렬하기
A :

select cuisine_type, MAX(price) max_price, MIN(price) min_price 
from food_orders
group by cuisine_type 
order by price