#49. GROUP BY : 조건에 맞는 사용자와 총 거래금액 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제

 

입력 코드

1. WHERE 

SELECT 
    USER.USER_ID, 
    USER.NICKNAME,
    SUM(BOARD.PRICE) AS TOTAL_SALES

FROM USED_GOODS_BOARD AS BOARD
    JOIN USED_GOODS_USER AS USER
    ON BOARD.WRITER_ID = USER.USER_ID
    
WHERE BOARD.STATUS = 'DONE'
GROUP BY BOARD.WRITER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC;

 
2. SUBQUERY

SELECT 
    U.USER_ID, U.NICKNAME, 
    SUM(PRICE) AS TOTAL_SALES
FROM (
    SELECT * 
    FROM USED_GOODS_BOARD 
    WHERE STATUS='DONE'
) AS B 
	JOIN USED_GOODS_USER AS U
	ON B.WRITER_ID = U.USER_ID
GROUP BY B.WRITER_ID
HAVING SUM(PRICE)>=700000
ORDER BY TOTAL_SALES;

 
3. JOIN을 사용하지 않고 WHERE 조건으로만 

SELECT 
    USER_ID, NICKNAME, 
    SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD, USED_GOODS_USER
WHERE (WRITER_ID = USER_ID) 
    AND (STATUS = "DONE")
GROUP BY USER_ID 
HAVING SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES ASC;

 

코드 설명

#SELECT #SUM #JOIN #WHERE #GROUP BY #HAVING #ORDER BY #SUBQUERY
 
 

문제 출처

https://school.programmers.co.kr/learn/courses/30/lessons/164668