using union all syntax with sum function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using union all syntax with sum function

Author

Topic

fdtoo
Starting Member

28 Posts

Posted - 2006-07-23 : 21:02:18

I have the following fields in Table A running in MS SQL Server 2000:

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 |0 |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:

|616 | 641 |
============================
Gross Profit |150 | 180

PRODMGR |616 | 641
===========================
LN |150 |--> (250-100)
OT | 0 | 180--> (300-120}

My query is as follows:

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?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-23 : 21:15:23

[code]select PRODMGR,
sum(case when SUBSTRING(MINOR,2,3) = '616' then REV_USD - CST_USD else 0 end) as [616],
sum(case when SUBSTRING(MINOR,2,3) = '641' then REV_USD - CST_USD else 0 end) as [641]
from TableA
where LCTRYNUM = '834'
and MAJOR IN ('538','548')
and AMT_TYPE = 'I'
group by PRODMGR[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-24 : 07:39:59

See also

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=296604
http://p2p.wrox.com/topic.asp?TOPIC_ID=47422

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources