SUM & GROUP BY 질문좀 부탁드립니닷! 0 4 2,692

by 제로나인 [MySQL] mysql group by sum sum group by [2016.04.19 15:06:46]


data.csv (57,492Bytes)

# 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 한뒤의 데이터입니다..

by 마농 [2016.04.19 15:39:08]

우선 Group By 사용법이 틀렸습니다.
Group By 기준항목이 아닌 항목들은 집계함수를 사용하여 조회해야 하는데
그냥 사용했네요. MySql 에서는 에러 안나지만 다른 DB 에서는 에러납니다.


서브쿼리 안쪽 쿼리가 궁급합니다.
서브쿼리 1차 결과를 다시 2차 가공하기 보다는
서브쿼리를 고쳐서 원하는 결과를 바로 뽑는게 좋을 때가 많습니다.


by 제로나인 [2016.04.19 17:02:28]

이런 쿼리를 사용했습니다.
어떻게 고쳐야할까요?
상단에 데이터 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;

 


by 마농 [2016.04.20 12:46:47]
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
;

 


by 제로나인 [2016.04.21 09:48:13]

와.......... 마농님 감사합니다!!!!

마농님의 쿼리를 보고 한수 배워갑니다. 감사합니다!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입