이번에는 Group by 문이다.
# 고양이와 개는 몇마리 있을까
동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.
SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC- 이번에도 어김없이 개수를 구해줄 때면 count(*)를 쓴다.
# 진료과별 총 예약횟수 출력하기
APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성해주세요. 이때, 컬럼명은 '진료과 코드', '5월예약건수'로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬해주세요.
SELECT MCDP_CD AS '진료과코드', COUNT(MCDP_CD) AS '5월예약건수'
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD, '%Y-%m') = '2022-05'
GROUP BY MCDP_CD
ORDER BY 5월예약건수, 진료과코드- 5월로만 필터링 걸어서 해주면 된다.
# 가격대 별 상품 개수 구하기
PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.
-- 코드를 입력하세요
SELECT CASE
WHEN PRICE < 10000 THEN '0'
ELSE TRUNCATE(PRICE,-4)
END AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
- 반올림할 때는 ROUND, 버림할 때는 TRUNCATE 함수를 써서, 몇번째자리에서 실행해줄 지 적으면 된다.
# 년, 월, 성별 별 상품 구매 회원 수 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, U.GENDER AS GENDER, COUNT(DISTINCT(U.USER_ID)) AS USERS
FROM USER_INFO AS U INNER JOIN ONLINE_SALE AS O
ON U.USER_ID = O.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE), GENDER
ORDER BY YEAR, MONTH, GENDER
- 이때 구매한 '거래수'를 구하라고 한거면 count(*)해주면 되는건데, '회원수'를 구하라고 하는건 중복구매하면 하나의 회원으로 카운트해야한다는 뜻. 그래서 이때는 distinct함수 써서 count(distinct(userid)) 해줘야함
- inner join한 경우랑 거래데이터 기준 left join한 경우랑 둘 다 결과는 정답으로 나오긴 하는데, 차이점은 inner join할 때는 online_sale 테이블에 user_info테이블 정보가 다 들어있다는 가정하에 들어가야하는거다. 만약 online_sale에는 id가 있는데 user_id에는 정보가 사라져있으면 inner는 있는 정보로만 하는거고 left는 없는대로 하는거다.
# 저자 별 카테고리 별 매출액 집계하기
-- 코드를 입력하세요
SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, B.PRICE*SUM(BS.SALES) AS TOTAL_SALES
FROM BOOK AS B
INNER JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
INNER JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID
WHERE DATE_FORMAT(BS.SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID, B.CATEGORY DESC- 3개 이상의 테이블을 서로 join하려고 할 때에는 기준점을 잡을 테이블을 앞에 두고 inner join ~ on, inner join ~ on 으로 적어주기
# 식품분류별 가장 비싼 식품의 정보 조회하기
FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE)
FROM FOOD_PRODUCT
GROUP BY CATEGORY) AND
CATEGORY IN('과자', '국', '김치', '식용유')
ORDER BY MAX_PRICE DESC- where ~ in ~ 을 쓸 때, 만들어둔 테이블에 칼럼이 2개 이상이면 where 다음에 ()로 묶어서 2개 이상의 변수명을 써주면 된다.