Ways to consolidate my years and months to get total qty per client, per month, per year

Question

I am trying to see the total qty by client, by month, by year. I have added to the group by clause, but doesn't really break it down how I am wanting. Pretty new to SQL. Any tips?

Tried grouping in the openquery and 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

These are the results I am trying to achieve. I have added actual results, vs results that I am expecting.

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

Answer 1

Don't put ORDER_QTY in your GROUP BY, instead use SUM(ORDER_QTY) in your 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

In-fact, just do it all in the remote location?

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
        ')