CASE
WHEN 조건1 THEN 결과값1
WHEN 조건2 THEN 결과값2
WHEN 조건N THEN 결과값N
ELSE 결과값
END
SELECT CASE
WHEN categoryid =1 AND SupplierID = 1 THEN '음료'
WHEN categoryid =2 THEN '조미료'
ELSE '기타'
END AS 'categoryName', *
FROM Products
결과:
카테고리 1,2,3번의 가격 평균만 보고 싶다.
SELECT AVG(CASE WHEN categoryid =1 THEN price ELSE NULL END) AS category1_price,
AVG(CASE WHEN categoryid =2 THEN price ELSE NULL END) AS category2_price,
AVG(CASE WHEN categoryid =3 THEN price ELSE NULL END) AS category3_price
FROM Products
- 결과:
SELECT CASE WHEN A=B AND B=C THEN 'Equilateral'
WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'
WHEN A=B OR B=C OR A=C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM triangles
생각해야 할 점:
- select문(프린트 되어 보이는 곳)에는 id, Jan_Revenue,..., Dec_Revenue로 들어간다.
- 여기서 Jan_Revenue는 AS 함수로 임의로 만든것 같다.
- CASE 문: when = '각 month' THEN revenue가 된다.
- SUM 사용: 1번 부서(id)의 각 month revenue를 보고싶은 거니까 case문을 SUM으로 묶기
- GROUP BY: 각 부서별로 나와 있으니까 GROUP BY id를 써준다.
SELECT id,
SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue ELSE NULL END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM department
GROUP BY id