using union all syntax with sum function

fdtoo's Avatar, Join Date: Apr 2006
Go4Expert Member
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's Avatar, Join Date: Jul 2004
Go4Expert Founder
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