Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   using union all syntax with sum function (http://www.go4expert.com/forums/using-union-syntax-sum-function-t1054/)

fdtoo 24Jul2006 06:12

using union all syntax with sum function
 
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: SQL

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?

shabbir 24Jul2006 11:05

Re: using union all syntax with sum function
 
I couldn't get what your query does but you can try something like

Code: SQL

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



All times are GMT +5.5. The time now is 17:51.