Блог

Я пытаюсь увидеть общее количество по клиенту, по месяцам, по годам. Я добавил в предложение group by, но на самом деле не разбивает его так, как я хочу. Довольно новичок в SQL. Есть какие-нибудь советы?

Пробовал группировать в openquery и sql

 select YEAR_    
,MONTH_
,BILL_TO
,ORDER_QTY

from openquery(TESTSVR,'
select OD.QTY AS ORDER_QTY
    ,CASE 
        WHEN OH.BILLING = ''12345'' 
            THEN ''TESTING''
        WHEN OH.BILLING = ''98765''
            THEN ''TESTING1''
        WHEN OH.BILLING = ''15973''
            THEN ''TESTING2''
    END AS BILL_TO
    ,TO_CHAR(CRDT, ''MONTH'') AS MONTH_
    ,TO_CHAR(CRDT, ''YYYY'') AS YEAR_

from TEST.TESTSVR OD

LEFT JOIN TEST.TESTSVR2 OH
    ON OD.ORDER = OH.ORDER

WHERE TO_CHAR(OH.CRDT, ''YYYY-MM-DD'') >= ''2019-01-01''
    AND  OD.SPCD = ''SPECIAL CODE 1''
    AND OH.BILLING IN (''12345'',''98765'',''15973'')

GROUP BY TO_CHAR(OHCRDT, ''YYYY'')
    ,TO_CHAR(OHCRDT, ''MONTH'')
    ,CASE 
        WHEN OH.BILLING = ''12345'' 
            THEN ''TESTING''
        WHEN OH.BILLING = ''98765''
            THEN ''TESTING1''
        WHEN OH.BILLING = ''15973''
            THEN ''TESTING2''
    END
    ,OD.QTY
') 
GROUP BY YEAR_
    , MONTH_
    , BILL_TO
    , ORDER_QTY
  

Это результаты, которых я пытаюсь достичь. Я добавил фактические результаты по сравнению с результатами, которые я ожидаю.

 Actual:

Year_  Month_  Bill_To  Order_qty
2019  January  Testing  5
2019  March    Testing  4
2019  February Testing  4
2019  January  Testing1 5
2019  March    Testing  9
2019  January  Testing  7
2019  January  Testing2 8


Wanting:

Year_  Month_  Bill_To  Order_qty
2019   January  Testing  12
2019   January  Testing1 5
2019   January  Testing2 8
2019   February Testing  4
2019   February Testing1 8
2019   February Testing2 8
  

Не вводите ORDER_QTY в свой GROUP BY , вместо этого используйте SUM(ORDER_QTY) в своем SELECT …

 SELECT
    YEAR_
   ,MONTH_
   ,BILL_TO
   ,SUM(ORDER_QTY) AS ORDER_QTY
FROM
    openquery(TESTSVR,'
        select OD.QTY AS ORDER_QTY
            ,CASE 
                WHEN OH.BILLING = ''12345'' 
                    THEN ''TESTING''
                WHEN OH.BILLING = ''98765''
                    THEN ''TESTING1''
                WHEN OH.BILLING = ''15973''
                    THEN ''TESTING2''
            END AS BILL_TO
            ,TO_CHAR(CRDT, ''MONTH'') AS MONTH_
            ,TO_CHAR(CRDT, ''YYYY'') AS YEAR_

        from TEST.TESTSVR OD

        LEFT JOIN TEST.TESTSVR2 OH
            ON OD.ORDER = OH.ORDER

        WHERE TO_CHAR(OH.CRDT, ''YYYY-MM-DD'') >= ''2019-01-01''
            AND  OD.SPCD = ''SPECIAL CODE 1''
            AND OH.BILLING IN (''12345'',''98765'',''15973'')

        GROUP BY TO_CHAR(OHCRDT, ''YYYY'')
            ,TO_CHAR(OHCRDT, ''MONTH'')
            ,CASE 
                WHEN OH.BILLING = ''12345'' 
                    THEN ''TESTING''
                WHEN OH.BILLING = ''98765''
                    THEN ''TESTING1''
                WHEN OH.BILLING = ''15973''
                    THEN ''TESTING2''
            END
            ,OD.QTY
        ')
GROUP BY
      YEAR_
    , MONTH_
    , BILL_TO
  

На самом деле, просто сделать все это в удаленном расположении?

 SELECT
    *
FROM
    openquery(TESTSVR,'
        SELECT
            ,TO_CHAR(CRDT, ''MONTH'') AS MONTH_
            ,TO_CHAR(CRDT, ''YYYY'') AS YEAR_
            ,CASE 
                WHEN OH.BILLING = ''12345'' 
                    THEN ''TESTING''
                WHEN OH.BILLING = ''98765''
                    THEN ''TESTING1''
                WHEN OH.BILLING = ''15973''
                    THEN ''TESTING2''
             END
                    AS BILL_TO,
             SUM(OD.QTY) AS ORDER_QTY
        FROM
            TEST.TESTSVR OD
        LEFT JOIN
            TEST.TESTSVR2 OH
                ON OD.ORDER = OH.ORDER
        WHERE
                TO_CHAR(OH.CRDT, ''YYYY-MM-DD'') >= ''2019-01-01''
            AND OD.SPCD = ''SPECIAL CODE 1''
            AND OH.BILLING IN (''12345'',''98765'',''15973'')
        GROUP BY
             TO_CHAR(OHCRDT, ''YYYY'')
            ,TO_CHAR(OHCRDT, ''MONTH'')
            ,CASE 
                WHEN OH.BILLING = ''12345'' 
                    THEN ''TESTING''
                WHEN OH.BILLING = ''98765''
                    THEN ''TESTING1''
                WHEN OH.BILLING = ''15973''
                    THEN ''TESTING2''
            END
        ')