select문 두개 합치는법
본문
SELECT mem_id,is_prime, is_biz_prime ,COUNT(pay_dtime) AS 8월결제건수 ,SUM(pay_amt) AS 8월금액합 FROM tb_pay
WHERE mem_id IN ('248210','247887','246026','245621','244150','243192','243186','242500','242425','242410','242234','241958','241814','241227','240667','239769','239660','238866','235910','235833','235271','234793','234620','234566','233907','233629','233618','233460','232637','232051','231569','231564','231451','231298','230965','230552','230542','230320','230176','229319','229312','229111','228959','228891','228511','228061','227958','227901','227477','227355','226651','226371','226068','224533','222780','220273','219240','218196','209034','205476','205453','204892','204681','204420','203996','81569')
AND pay_dtime >= '2021-08-01 00:00:00' AND pay_dtime <='2021-08-31 23:59:59'
GROUP BY mem_id;
SELECT mem_id,is_prime, is_biz_prime ,COUNT(pay_dtime) AS 9월결제건수 ,SUM(pay_amt) AS 9월금액합 FROM tb_pay
WHERE mem_id IN ('248210','247887','246026','245621','244150','243192','243186','242500','242425','242410','242234','241958','241814','241227','240667','239769','239660','238866','235910','235833','235271','234793','234620','234566','233907','233629','233618','233460','232637','232051','231569','231564','231451','231298','230965','230552','230542','230320','230176','229319','229312','229111','228959','228891','228511','228061','227958','227901','227477','227355','226651','226371','226068','224533','222780','220273','219240','218196','209034','205476','205453','204892','204681','204420','203996','81569')
AND pay_dtime >= '2021-09-01 00:00:00' AND pay_dtime <='2021-09-31 23:59:59'
GROUP BY mem_id;
공부하다가 두 결과값을 | 2021-08월 카운트 | 금액 | 09카운트 | 금액 | 10카운트 | 금액 | 이런식으로 합치고 싶은데요 흠... 잘 합쳐지지 않아서 궁금해서 질문 올려봅니다.. 혹시 제가 답변보다 빨리 해결하게 되면 댓글로 남겨 두겠습니다.. 감사합니다~~
답변 3
1. 기간을 기준으로 group by 해서 각 월에 대한 카운트를 구한다.
2. 1월부터 12월까지 1월, 1월합계, 2월, 2월합계 ... 컬럼을 만들되..
1월 합계는 sum(if(mon = "1"), cnt, 0)
2월 합계는 sum(if(mon = "2"), cnt, 0) 이런식으로 모든 월에 대해 만듭니다.
쉽게 하자면
union을 쓰시면 되지 않을까요?
SELECT mem_id,is_prime, is_biz_prime ,COUNT(pay_dtime) AS 8월결제건수 ,SUM(pay_amt) AS 8월금액합 FROM tb_pay
WHERE mem_id IN ('248210','247887','246026','245621','244150','243192','243186','242500','242425','242410','242234','241958','241814','241227','240667','239769','239660','238866','235910','235833','235271','234793','234620','234566','233907','233629','233618','233460','232637','232051','231569','231564','231451','231298','230965','230552','230542','230320','230176','229319','229312','229111','228959','228891','228511','228061','227958','227901','227477','227355','226651','226371','226068','224533','222780','220273','219240','218196','209034','205476','205453','204892','204681','204420','203996','81569')
AND pay_dtime >= '2021-08-01 00:00:00' AND pay_dtime <='2021-08-31 23:59:59'
GROUP BY mem_id;
union all
SELECT mem_id,is_prime, is_biz_prime ,COUNT(pay_dtime) AS 9월결제건수 ,SUM(pay_amt) AS 9월금액합 FROM tb_pay
WHERE mem_id IN ('248210','247887','246026','245621','244150','243192','243186','242500','242425','242410','242234','241958','241814','241227','240667','239769','239660','238866','235910','235833','235271','234793','234620','234566','233907','233629','233618','233460','232637','232051','231569','231564','231451','231298','230965','230552','230542','230320','230176','229319','229312','229111','228959','228891','228511','228061','227958','227901','227477','227355','226651','226371','226068','224533','222780','220273','219240','218196','209034','205476','205453','204892','204681','204420','203996','81569')
AND pay_dtime >= '2021-09-01 00:00:00' AND pay_dtime <='2021-09-31 23:59:59'
GROUP BY mem_id;
이렇게 해보세요
답변을 작성하시기 전에 로그인 해주세요.
