CODEWITHSUNDEEP
oracle-databaseplsqlunion
Captain_Teemo
Captain_Teemo

Reputation: 29

How to subtract in oracle using union

SELECT A
FROM   (
        SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) as A --247011
        FROM   RE_STOCK_TB B
        WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
          AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
           IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                        COMPANY_ID, INPUT_NO, STOCK_DATE 
                 FROM   RE_STOCK_TB 
                 WHERE  COMPANY_ID  = B.COMPANY_ID 
                   AND  INPUT_NO    = B.INPUT_NO   
                   AND  STOCK_DATE <= :P_KEY_DATE
                   AND  ROWNUM      = 1
               ) 
          AND  B.RMN_WGT            > 0 
          AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
          AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
        UNION
        SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) --246385
        FROM   RE_STOCK_TB B
        WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
          AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
           IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                        COMPANY_ID, INPUT_NO, STOCK_DATE 
                 FROM   RE_STOCK_TB 
                 WHERE  COMPANY_ID  = B.COMPANY_ID 
                   AND  INPUT_NO    = B.INPUT_NO   
                   AND  STOCK_DATE <= :P_KEY_DATE
                   AND  ROWNUM      = 1
               ) 
          AND  B.RMN_WGT            > 0 
          AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
          AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
          AND  B.SIZE1              IN (0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 1.5, 2.0))

enter image description here

Hello, I am using UNION and want to subtract those two values on column A. Is there any way to do it? Like opposite of SUM. I have looked on google but can't find any answers..

Upvotes: 0

Views: 236

Answers (1)

Alfin E. R.
Alfin E. R.

Reputation: 928

there are a lot of ways

One of the way is by * -1 for the columns A at the selection after union

SELECT SUM(A)
FROM   (
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) as A --247011
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
    UNION
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) * -1 --246385
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
      AND  B.SIZE1              IN (0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 
1.5, 2.0))

Or conditional sum by adding a surrogate column for flagging

SELECT SUM(IIF(A = 2, A * -1, A)
FROM   (
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))) as A, '1' AS FLAG --247011
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
    UNION
    SELECT TRUNC(SUM(NVL(B.RMN_WGT,0))), '2' AS FLAG --246385
    FROM   RE_STOCK_TB B
    WHERE  B.COMPANY_ID = :P_COMPANY_ID                                                                         
      AND  ( B.COMPANY_ID, B.INPUT_NO, B.STOCK_DATE ) 
       IN  ( SELECT /*+ INDEX_DESC( RE_STOCK_TB RE_STOCK_PK ) */
                    COMPANY_ID, INPUT_NO, STOCK_DATE 
             FROM   RE_STOCK_TB 
             WHERE  COMPANY_ID  = B.COMPANY_ID 
               AND  INPUT_NO    = B.INPUT_NO   
               AND  STOCK_DATE <= :P_KEY_DATE
               AND  ROWNUM      = 1
           ) 
      AND  B.RMN_WGT            > 0 
      AND  B.GOODS_CD           IN ('PG')  --('PG','CC','CK')
      AND  B.STEEL_KIND_CD      IN ('304') --('304','201') 
      AND  B.SIZE1              IN (0.5, 0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 
1.5, 2.0))

Upvotes: 2

Related Questions