안녕하세요.
SELECT a.item_name as "품명"
, SUM(b.amount) as "1월"
FROM burger_ord_item b
JOIN burger_item a
ON a.item_code = b.item_code
JOIN burger_ord c
ON b.ord_code = c.ord_code
where c.ord_date between 20170101 and 20170131
GROUP BY a.item_name;
이렇게 쿼리를 짜서 1월달 품목별 매출을 나오게 했는데
2~12월까지 가로 테이블로 조회 되게끔 쿼리를 짜고 싶은데
어떻게 하면 될까요...도와주세요 ㅠ
SELECT a.item_name , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '01' THEN b.amount END) m01 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '02' THEN b.amount END) m02 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '03' THEN b.amount END) m03 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '04' THEN b.amount END) m04 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '05' THEN b.amount END) m05 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '06' THEN b.amount END) m06 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '07' THEN b.amount END) m07 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '08' THEN b.amount END) m08 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '09' THEN b.amount END) m09 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '10' THEN b.amount END) m10 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '11' THEN b.amount END) m11 , SUM(CASE SUBSTR(c.ord_date, 5, 2) WHEN '12' THEN b.amount END) m12 , SUM(b.amount) tot FROM burger_item a INNER JOIN burger_ord_item b ON a.item_code = b.item_code INNER JOIN burger_ord c ON b.ord_code = c.ord_code WHERE c.ord_date BETWEEN '20170101' AND '20171231' GROUP BY a.item_code, a.item_name ;