[프로그래머스.SQL] GROUP BY

즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN 
(SELECT FOOD_TYPE, MAX(FAVORITES) FROM REST_INFO GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

 

고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) as count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

SELECT ANIMAL_TYPE, COUNT(*) as count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

 

동명 동물 수 찾기

SELECT NAME, COUNT(NAME) as COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME;

 

년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(s.SALES_DATE) as YEAR, MONTH(s.SALES_DATE) as MONTH,  u.GENDER as GENDER, COUNT(DISTINCT(u.USER_ID)) as USERS
FROM USER_INFO as u
    JOIN ONLINE_SALE as s
    ON u.USER_ID = s.USER_ID
WHERE u.GENDER IS NOT NULL
GROUP BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE), u.GENDER;



SELECT YEAR(s.SALES_DATE) as YEAR, 
        MONTH(s.SALES_DATE) as MONTH,
        u.GENDER as GENDER, COUNT(DISTINCT s.USER_ID) as USERS
FROM ONLINE_SALE as s
    LEFT JOIN USER_INFO as u
    ON s.USER_ID = u.USER_ID
    WHERE u.GENDER IS NOT NULL
GROUP BY YEAR(s.SALES_DATE), MONTH(s.SALES_DATE), u.GENDER
ORDER BY YEAR, MONTH, GENDER;

 

입양 시각 구하기(1)

SELECT HOUR(DATETIME) as HOUR, COUNT(DATETIME) as COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR;

 

입양 시각 구하기(2)

SET 함수 사용 

WITH RECURSIVE

SET 함수 사용
SET @변수 = 값 set 사용 시에만 =
@변수 := 값 그 이후 대입에서는 := 

SET @hour = -1;

SELECT (@hour := @hour + 1) AS HOUR, 
    (SELECT COUNT(HOUR(DATETIME))
     FROM ANIMAL_OUTS 
     WHERE HOUR(DATETIME) = @hour)
FROM ANIMAL_OUTS
WHERE @hour < 23;


WITH RECURSIVE 테이블 이름
AS ( 
    SELECT 1 AS n // 비반복, 처음 한 번 실행
    UNION ALL
   
    SELECT n + 1 AS n // 반복, 행 위치 기억하여 다음 행에서 계속 실행됨
    FROM 테이블 이름
    WHERE n < 10 // 종료 조건
)

WITH RECURSIVE CTE AS
(
    SELECT 0 as HOUR
    UNION ALL
    
    SELECT HOUR + 1 AS HOUR
    FROM CTE
    WHERE HOUR < 23
)

# SELECT c.HOUR, CASE WHEN IFNULL(COUNT(DATETIME), 0) 
SELECT c.HOUR, COUNT(a.DATETIME) as COUNT
FROM CTE as c 
    LEFT JOIN ANIMAL_OUTS as a
    ON c.HOUR = HOUR(a.DATETIME)
GROUP BY c.HOUR
ORDER BY c.HOUR;

 

가격대 별 상품개수 구하기

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(PRICE) as PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

SELECT PRICE DIV 10000 * 10000 AS PRICE_GROUP, COUNT(PRICE) as PRODUCTS
FROM PRODUCT
GROUP BY PRICE DIV 10000
ORDER BY PRICE DIV 10000;