[sql] 조건에 맞는 사용자와 총 거래금액 조회하기

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

initial

i think its simple too even tho its level 3 but i took some time to implment this.

im thinking inner subquery to sum the values that have status "done" for each writer id via group by writer id and join that table with the USED_GOODS_USER on user_id

sol

so u can do either inner subquery or a direct join. I didnt think of a direct join in main query so i tried doing that way but

SELECT a.writer_id, b.nickname, sum(a.price) as price
from USED_GOODS_BOARD a
where a.status ='DONE'
group by a.writer_id
having price >= 700000
join USED_GOODS_USER b
on a.writer_id = b.user_id

this is sql syntax error. join needs to come before where

The correct order is:

FROM
JOIN ← must be here
WHERE
GROUP BY
HAVING
ORDER BY

You have JOIN after HAVING, which violates SQL syntax rules.

main query

also we cant use the selected alias like (user_id) on the on condition.

SELECT a.writer_id as user_id, b.nickname, sum(a.price) as total_sales
FROM USED_GOODS_BOARD a              -- 1. Start here
JOIN USED_GOODS_USER b               -- 2. Join tables
ON a.writer_id = b.user_id           -- 3. Join condition
WHERE a.status = 'DONE'              -- 4. Filter rows
GROUP BY a.writer_id, b.nickname     -- 5. Group filtered rows
HAVING total_sales >= 700000         -- 6. Filter groups
ORDER BY total_sales ASC             -- 7. Sort result
SELECT ...                           -- 8. Finally, select columns

notice the select runs LAST

while u can technically use 'total_sales' alias from having statement onwards cuz

FROM/JOIN
WHERE
GROUP BY
HAVING ← processes AFTER SELECT logically
SELECT ← aliases created here
ORDER BY ← processes AFTER SELECT

just for simplicity sake dont use alias within the query

subquery

SELECT sub.writer_id AS user_id, 
       u.nickname, 
       sub.total_sales
FROM (
    SELECT writer_id, 
           SUM(price) AS total_sales
    FROM USED_GOODS_BOARD
    WHERE status = 'DONE'
    GROUP BY writer_id
    HAVING SUM(price) >= 700000
) sub
JOIN USED_GOODS_USER u
ON sub.writer_id = u.user_id
ORDER BY sub.total_sales ASC;