데이터베이스 질문과 답변
by 제로나인 [MySQL] mysql group by sum sum group by [2016.04.19 15:06:46]
# startMonth, endMonth, advertiserId, advertiserBrandId, monthly_reportMonth, adv_name, adv_monthly_sales, adv_sales, brd_name, brd_monthly_sales, brd_sales
2016-01, 2016-04, 5, 2615, 2016-02, (주)SK네트웍스, 11500000, 81500000, , ,
2016-01, 2016-04, 5, 2615, 2016-03, (주)SK네트웍스, 22000000, 81500000, , ,
2016-01, 2016-04, 5, 2615, 2016-04, (주)SK네트웍스, 48000000, 81500000, , ,
2016-01, 2016-04, 5, 2615, 2016-02, (주)SK네트웍스, , , SK패션몰, 10000000, 60000000
2016-01, 2016-04, 5, 2615, 2016-03, (주)SK네트웍스, , , SK패션몰, 22000000, 60000000
2016-01, 2016-04, 5, 2615, 2016-04, (주)SK네트웍스, , , SK패션몰, 28000000, 60000000
2016-01, 2016-04, 5, 2583, 2016-02, (주)SK네트웍스, , , 오즈세컨, 1500000, 21500000
2016-01, 2016-04, 5, 2583, 2016-04, (주)SK네트웍스, , , 오즈세컨, 20000000, 21500000
이러한 데이터가 있습니다.
SELECT
'2016-01' AS startMonth,
'2016-04' AS endMonth,
advertiserId,
advertiserBrandId,
adv_monthly_reportMonth,
brd_monthly_reportMonth,
advertiserName as adv_name,
sum(adv_monthly_sales) as adv_monthly_sales,
sum(adv_sales) as adv_sales,
advertiserBrandName as brd_name,
sum(brd_monthly_sales) as brd_monthly_sales,
sum(brd_sales) as brd_sales
FROM(
.....)
GROUP BY brd_monthly_reportMonth, advertiserBrandId
위의 쿼리로 아래의 결과가 나왔는데요..
아래의 쿼리중에 노란색 부분에도 같은달에 해당하는 금액을 보여주고싶은데
어떻게 해야할까요?...
제가 원하는 결과는
위의 노란색 부분에 11500000 81500000
아래의 노란 부분에 48000000 81500000
라고 표시되고싶습니다..
2016-01 2016-04 5 2615 2016-02 2016-02 (주)SK네트웍스 11500000 81500000 SK패션몰 10000000 60000000
2016-01 2016-04 5 2615 2016-03 2016-03 (주)SK네트웍스 22000000 81500000 SK패션몰 22000000 60000000
2016-01 2016-04 5 2615 2016-04 2016-04 (주)SK네트웍스 48000000 81500000 SK패션몰 28000000 60000000
2016-01 2016-04 5 2583 2016-02 2016-02 (주)SK네트웍스 0 0 오즈세컨 1500000 21500000
2016-01 2016-04 5 2583 2016-04 2016-04 (주)SK네트웍스 0 0 오즈세컨 20000000 21500000
p.s - advertiser의 월별 금액 및 월별 합계와 brand의 월별 금액 및 월별 합계를 구한 2개의 테이블을 UNION ALL 한뒤의 데이터입니다..
우선 Group By 사용법이 틀렸습니다.
Group By 기준항목이 아닌 항목들은 집계함수를 사용하여 조회해야 하는데
그냥 사용했네요. MySql 에서는 에러 안나지만 다른 DB 에서는 에러납니다.
서브쿼리 안쪽 쿼리가 궁급합니다.
서브쿼리 1차 결과를 다시 2차 가공하기 보다는
서브쿼리를 고쳐서 원하는 결과를 바로 뽑는게 좋을 때가 많습니다.
이런 쿼리를 사용했습니다.
어떻게 고쳐야할까요?
상단에 데이터 csv 파일을 첨부하였습니다..
도움좀 부탁드립니다..!!
SELECT
'2016-01' AS startMonth,
'2016-04' AS endMonth,
advertiserId,
advertiserBrandId,
monthly_reportMonth,
advertiserName as adv_name,
adv_monthly_sales,
adv_sales as adv_TotalSales,
advertiserBrandName as brd_name,
brd_monthly_sales,
brd_sales as brd_TotalSales
FROM(
SELECT
A.advertiser_id AS advertiserId,
A.advertiserBrand_id AS advertiserBrandId,
A.advertiserName AS advertiserName,
A.advertiserBrandName AS advertiserBrandName,
B.billDate AS monthly_reportMonth,
B.sales as adv_monthly_sales,
A.totalSales as adv_sales,
0 AS brd_monthly_sales,
0 AS brd_sales
FROM
(SELECT
advertiser.advertiser_id,
advertiserBrand.advertiserBrand_id,
advertiserBrand.name AS advertiserBrandName,
ADVcompany.name AS advertiserName,
ROUND(SUM(CASE CB.vatFlag
WHEN 1 THEN CM.mediaCost / 1.1
WHEN 2 THEN CM.mediaCost * 1
END)) AS totalSales
FROM
campaign_media AS CM
LEFT JOIN campaign C USING (campaign_id)
INNER JOIN campaignBill AS CB USING (campaign_id)
LEFT JOIN advertiserBrand ON (C.advertiserBrand_id = advertiserBrand.advertiserBrand_id)
LEFT JOIN advertiser USING (advertiser_id)
LEFT JOIN company AS ADVcompany ON (advertiser.company_id = ADVcompany.company_id)
WHERE
LEFT(CM.billDate, 7) BETWEEN '2016-01' AND '2016-04'
AND C.deleteFlag = '0'
AND C.saleCode = 1
GROUP BY ADVcompany.name-- , advertiserBrand.advertiserBrand_id
) A
JOIN
(SELECT
advertiser.advertiser_id,
advertiserBrand.advertiserBrand_id,
advertiserBrand.name AS advertiserBrandName,
ADVcompany.name AS advertiserName,
LEFT(CM.billDate, 7) AS billDate,
ROUND(SUM(CASE CB.vatFlag
WHEN 1 THEN CM.mediaCost / 1.1
WHEN 2 THEN CM.mediaCost * 1
END)) AS sales
FROM
campaign_media AS CM
LEFT JOIN campaign C USING (campaign_id)
INNER JOIN campaignBill AS CB USING (campaign_id)
LEFT JOIN advertiserBrand ON (C.advertiserBrand_id = advertiserBrand.advertiserBrand_id)
LEFT JOIN advertiser USING (advertiser_id)
LEFT JOIN company AS ADVcompany ON (advertiser.company_id = ADVcompany.company_id)
WHERE
LEFT(CM.billDate, 7) BETWEEN '2016-01' AND '2016-04'
AND C.deleteFlag = '0'
AND C.saleCode = 1
GROUP BY ADVcompany.name, LEFT(billDate, 7)) B
ON B.advertiser_Id = A.advertiser_Id
UNION ALL
SELECT
A.advertiser_id AS advertiserId,
A.advertiserBrand_id AS advertiserBrandId,
A.advertiserName AS advertiserName,
A.advertiserBrandName AS advertiserBrandName,
B.billDate AS monthly_reportMonth,
0 as adv_monthly_sales,
0 as adv_sales,
B.sales AS brd_monthly_sales,
A.totalSales AS brd_sales
FROM
(SELECT
advertiser.advertiser_id,
advertiserBrand.advertiserBrand_id,
advertiserBrand.name AS advertiserBrandName,
ADVcompany.name AS advertiserName,
ROUND(SUM(CASE CB.vatFlag
WHEN 1 THEN CM.mediaCost / 1.1
WHEN 2 THEN CM.mediaCost * 1
END)) AS totalSales
FROM
campaign_media AS CM
LEFT JOIN campaign C USING (campaign_id)
INNER JOIN campaignBill AS CB USING (campaign_id)
LEFT JOIN advertiserBrand ON (C.advertiserBrand_id = advertiserBrand.advertiserBrand_id)
LEFT JOIN advertiser USING (advertiser_id)
LEFT JOIN company AS ADVcompany ON (advertiser.company_id = ADVcompany.company_id)
WHERE
LEFT(CM.billDate, 7) BETWEEN '2016-01' AND '2016-04'
AND C.deleteFlag = 0
AND C.saleCode = 1
GROUP BY advertiserBrand.name) A
JOIN
(SELECT
advertiser.advertiser_id,
advertiserBrand.advertiserBrand_id,
advertiserBrand.name AS advertiserBrandName,
ADVcompany.name AS advertiserName,
LEFT(CM.billDate, 7) AS billDate,
ROUND(SUM(CASE CB.vatFlag
WHEN 1 THEN CM.mediaCost / 1.1
WHEN 2 THEN CM.mediaCost * 1
END)) AS sales
FROM
campaign_media AS CM
LEFT JOIN campaign C USING (campaign_id)
INNER JOIN campaignBill AS CB USING (campaign_id)
LEFT JOIN advertiserBrand ON (C.advertiserBrand_id = advertiserBrand.advertiserBrand_id)
LEFT JOIN advertiser USING (advertiser_id)
LEFT JOIN company AS ADVcompany ON (advertiser.company_id = ADVcompany.company_id)
WHERE
LEFT(CM.billDate, 7) BETWEEN '2016-01' AND '2016-04'
AND C.deleteFlag = 0
AND C.saleCode = 1
GROUP BY advertiserBrand.name, LEFT(billDate, 7)) B
ON B.advertiserBrand_id = A.advertiserBrand_id
) RESULT
GROUP BY advertiserBrandName, monthly_reportMonth
ORDER BY advertiserName ASC, advertiserBrandName ASC, monthly_reportMonth ASC;
SELECT '2016-01' AS startMonth , '2016-04' AS endMonth , a.adv_Id , a.adv_Nm , a.brd_Id , a.brd_Nm , a.bil_YM , ROUND(SUM(CASE WHEN a.bil_YM = b.bil_YM THEN b.Sales END)) AS adv_Monthly_Sales , ROUND(SUM(b.Sales)) AS adv_Total_Sales , ROUND(a.Sales) AS brd_Monthly_Sales , ROUND(SUM(CASE WHEN a.brd_Id = b.brd_Id THEN b.Sales END)) AS brd_Total_Sales FROM (SELECT a.advertiser_id AS adv_Id , ac.name AS adv_Nm , ab.advertiserBrand_id AS brd_Id , ab.name AS brd_Nm , LEFT(cm.billDate, 7) AS bil_YM , SUM(cm.mediaCost / CASE cb.vatFlag WHEN 1 THEN 1.1 WHEN 2 THEN 1 END) AS Sales FROM campaign_media AS cm LEFT JOIN campaign AS c USING (campaign_id) INNER JOIN campaignBill AS cb USING (campaign_id) LEFT JOIN advertiserBrand AS ab USING (advertiserBrand_id) LEFT JOIN advertiser AS a USING (advertiser_id) LEFT JOIN company AS ac USING (company_id) WHERE LEFT(cm.billDate, 7) BETWEEN '2016-01' AND '2016-04' AND c.deleteFlag = '0' AND c.saleCode = 1 GROUP BY a.advertiser_id, ac.name, ab.advertiserBrand_id, ab.name, LEFT(cm.billDate, 7) ) a INNER JOIN (SELECT a.advertiser_id AS adv_Id , ac.name AS adv_Nm , ab.advertiserBrand_id AS brd_Id , ab.name AS brd_Nm , LEFT(cm.billDate, 7) AS bil_YM , SUM(cm.mediaCost / CASE cb.vatFlag WHEN 1 THEN 1.1 WHEN 2 THEN 1 END) AS Sales FROM campaign_media AS cm LEFT JOIN campaign AS c USING (campaign_id) INNER JOIN campaignBill AS cb USING (campaign_id) LEFT JOIN advertiserBrand AS ab USING (advertiserBrand_id) LEFT JOIN advertiser AS a USING (advertiser_id) LEFT JOIN company AS ac USING (company_id) WHERE LEFT(cm.billDate, 7) BETWEEN '2016-01' AND '2016-04' AND c.deleteFlag = '0' AND c.saleCode = 1 GROUP BY a.advertiser_id, ac.name, ab.advertiserBrand_id, ab.name, LEFT(cm.billDate, 7) ) b ON a.adv_Id = b.adv_Id GROUP BY a.adv_Id, a.adv_Nm, a.brd_Id, a.brd_Nm, a.bil_YM, a.Sales ;
와.......... 마농님 감사합니다!!!!
마농님의 쿼리를 보고 한수 배워갑니다. 감사합니다!!
) 버튼을 클릭하여 작성 하시면 됩니다.