1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

using union all syntax with sum function

Discussion in 'SQL Server' started by fdtoo, Jul 24, 2006.

  1. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    I have the following fields in Table A running in MS SQL Server 2000:
    Code:
    MAJOR|	MINOR|	PRODMGR|CST_USD	|REV_USD|LCTRYNUM |AMT_TYPE
    ============================================================
    538  |	1616 |	LN     |0	|250	|834	  |  I
    538  |	1641 |	OT     |0	|300	|834	  |  I
    548  |	1616 |	LN     |100	|0	|834	  |  I
    548  |	1641 |	OT     |120	|834	  |  I
    400  |	0100 |	LV     |50	|0	|888	  |  D
    402  |	0200 |	LO     |80	|0	|888	  |  D
    404  |	0110 |	LJ     |30	|0	|333	  |  J
    
    I would like to query these fields so that i can have the below result:
    Code:
                  |616  |  641 | 
    ============================
    Gross Profit  |150  |  180 
    
    PRODMGR       |616  |  641 
    ===========================
    LN            |150  |--> (250-100)  
    OT            | 0   |  180--> (300-120} 
    
    My query is as follows:
    Code:
    SELECT
    SUM(REV_USD)-SUM(CST_USD) AS [616]
    FROM Table A
    WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
    STR(MINOR,2,3)='616' AND AMT_TYPE='I'
    UNION ALL
    SELECT
    SUM(REV_USD)-SUM(CST_USD) AS [641]
    FROM Table A
    WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
    STR(MINOR,2,3)='641' AND AMT_TYPE='I'
    UNION ALL
    SELECT DISTINCT PRODMGR,
    (SELECT SUM(REV_USD)-SUM(CST_USD) 
     FROM Table A
     WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
     STR(MINOR,2,3)='616' AND AMT_TYPE='I'AND PRODMGR=MAIN.PRODMGR) AS [616],
    (SELECT SUM(REV_USD)-SUM(CST_USD) 
     FROM Table A
     WHERE LCTRYNUM='834' AND MAJOR IN ('538','548') AND 
     STR(MINOR,2,3)='641' AND AMT_TYPE='I'AND PRODMGR=MAIN.PRODMGR) AS [641]
    FROM Table A AS MAIN
    I can't seem to get the result i want, can anyone help?
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    I couldn't get what your query does but you can try something like

    Code:
    SELECT
    PRODMGR, 
    SUM(REV_USD)-SUM(CST_USD) AS [616],
    SUM(REV_USD)-SUM(CST_USD) AS [641]
    FROM TABLE A
    WHERE PRODMGR IN ('LN','OT')
    GROUP BY PRODMGR 
     

Share This Page