결제테이블에서
상품,가격,결제일자
select sum(가격) from 상품테이블
이걸 상품별, 분기별로 조회하고싶습니다
결제일자를 분기별로 쿼리작성하려면 어떻게해야되나요?
pubs db에서 테스트 한 겁니다.
이것과 유사한 방식으로 하면 되지 않을까요.
날짜를 데이터타입에 따라 약간 달라질 수 있겠지만..
아래쪽은 연도가 주어졌을때 상점,분기별계를
크로스텝형태로 나열해 본 것입니다.
select a.stor_id, a.yyyy, a.quater, isnull(sum(a.qty), 0)from ( select a.stor_id, a.yyyy, case when a.mm between 1 and 3 then '1분기' when a.mm between 4 and 6 then '2분기' when a.mm between 7 and 9 then '3분기' when a.mm between 10 and 12 then '4분기' end quater, sum(a.qty) qty from ( select stor_id stor_id, year(ord_date) yyyy, month(ord_date) mm, sum(qty) qty from sales group by stor_id, year(ord_date), month(ord_date) ) a group by a.stor_id, a.yyyy, a.mm ) agroup by a.stor_id, a.yyyy, a.quaterorder by a.stor_id, a.yyyy, a.quater
select isnull(a.stor_id, '분기계') '상점', isnull(sum(case a.quater when '1분기' then a.qty end), 0) '1분기', isnull(sum(case a.quater when '2분기' then a.qty end), 0) '2분기', isnull(sum(case a.quater when '3분기' then a.qty end), 0) '3분기', isnull(sum(case a.quater when '4분기' then a.qty end), 0) '4분기', isnull(sum(a.qty), 0) '상점계'from ( select a.stor_id, case when a.mm between 1 and 3 then '1분기' when a.mm between 4 and 6 then '2분기' when a.mm between 7 and 9 then '3분기' when a.mm between 10 and 12 then '4분기' end quater, sum(a.qty) qty from ( select stor_id stor_id, month(ord_date) mm, sum(qty) qty from sales where ord_date between cast('1993-01-01' as datetime) and cast('1993-12-31' as datetime) group by stor_id, year(ord_date), month(ord_date) ) a group by a.stor_id, a.mm ) agroup by a.stor_id with rolluporder by isnull(a.stor_id, '총계')