데이터베이스 질문과 답변
구루비회원님들 안녕하세요.
쿼리를 짜는 도중에 막히는 부분이 있어 문의드립니다.
다음과 같은 테이블이 있습니다.
[1] 테이블명 : priceTable
| id | place | date | name | amount | price |
| 1 | 서울점 | 2019-01-15 | 사과 | 1 | 2000 |
| 2 | 여수점 | 2020-01-01 | 고구마 | 2 | 1000 |
| 3 | 서울점 | 2020-01-01 | 감자 | 1 | 2000 |
| 4 | 서울점 | 2020-01-01 | 사과 | 1 | 3000 |
| 5 | 서울점 | 2020-01-01 | 바나나 | 2 | 4000 |
| 6 | 천안점 | 2020-01-01 | 수박 | 1 | 300 |
| 7 | 서울점 | 2020-01-03 | 고구마 | 1 | 1000 |
| 8 | 서울점 | 2020-01-03 | 고구마 | 1 | 1000 |
| 9 | 여수점 | 2020-01-15 | 수박 | 1 | 300 |
저는 '일자별' +'지점별'로 데이터를 추출하고싶어 다음과 같은 쿼리를 사용하였습니다.
SELECT
date, place as '판매지점' ,
SUM(price*amount) as '총판매금액',
SUM(amount) as '총판매건수'
FROM tmp1.priceTable pt
where LEFT(date,7) = '2020-01'
group by pt.date,place;
나온 결과값
=>
| date | 판매지점 | 총판매금액 | 총판매건수 |
| 2020-01-01 | 서울점 | 13000 | 4 |
| 2020-01-01 | 여수점 | 2000 | 2 |
| 2020-01-01 | 천안점 | 300 | 1 |
| 2020-01-03 | 서울점 | 2000 | 2 |
| 2020-01-15 | 서울점 | 2000 | 1 |
| 2020-01-15 | 여수점 | 300 | 1 |
제가 원하는 결과값은 다음과같습니다.
| date | 서울점 총판매건수 | 서울점 총판매금액 | 천안점 총판매건수 | 천안점 총판매금액 | 여수점 총판매건수 | 여수점 총판매금액 |
| 2020-01-01 | 4 | 13000 | 1 | 300 | 2 | 2000 |
| 2020-01-03 | 2 | 2000 | 0 | 0 | 0 | 0 |
| 2020-01-15 | 1 | 2000 | 0 | 0 | 300 | 0 |
Q.1) 지점별 일별매출정보를 컬럼으로 놓고싶은데 가능할까요?
지점들이 100개 가량 된다면 CASE WHEN '서울점' , WHEN '천안점' ....100개의 when절을 일일이 적는 것도 아닌거같아서요ㅠ
Q.2) 만약에 해당 일자에 매출이 없는 지점은 0으로 표시가 가능한가요?
감사합니다. 좋은 하루보내세요!
1. 컬럼명을 date 와 같은 예약어를 사용하는 것은 좋지 않습니다.
2. 알리아스는 홑따옴표가 아닌 쌍따옴표 이용해 주세요.(표준, 타 DB 호환성)
3. 컬럼을 가공하여 비교하는 것은 좋지 않습니다.
- 컬럼은 그대로 두고 조건을 가공하여 비교하세요.
- 변경전 : WHERE LEFT(date, 7) = '2020-01'
- 변경후 : date LIKE '2020-01%'
4. 가변 컬럼 적용은
- 정적 SQL 로는 불가능합니다. 일일히 다 적어줘야 합니다.
- 가변 컬럼을 원한다면 동적 SQL 이용해야 합니다.
- 지점이 100 개 이상이라면?
- 지점을 펼치는 것보다는 일자를 펼치는 것을 추천합니다. (31개 고정)
SELECT date
, IFNULL(SUM(CASE place WHEN '서울점' THEN amount END), 0) 서울점_총판매건수
, IFNULL(SUM(CASE place WHEN '서울점' THEN amount * price END), 0) 서울점_총판매금액
, IFNULL(SUM(CASE place WHEN '천안점' THEN amount END), 0) 천안점_총판매건수
, IFNULL(SUM(CASE place WHEN '천안점' THEN amount * price END), 0) 천안점_총판매금액
, IFNULL(SUM(CASE place WHEN '여수점' THEN amount END), 0) 여수점_총판매건수
, IFNULL(SUM(CASE place WHEN '여수점' THEN amount * price END), 0) 여수점_총판매금액
, SUM(amount * price) 총판매금액
, SUM(amount) 총판매건수
FROM tmp1.priceTable
WHERE date LIKE '2020-01%'
GROUP BY date
;
) 버튼을 클릭하여 작성 하시면 됩니다.