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?
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