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?

SELECT 'Gross profit' 'ProdMgr',
            SUM(CASE WHEN RIGHT(MINOR, 3) = '616' THEN REV_USD - CST_USD ELSE 0 END) '616',
            SUM(CASE WHEN RIGHT(MINOR, 3) = '641' THEN REV_USD - CST_USD ELSE 0 END) '641'
FROM [table A]
WHERE LCTRYNUM = '834'
            AND MAJOR IN ('538', '548')
            AND AMT_TYPE = 'I'
UNION ALL

SELECT PRODMGR,
        SUM(CASE WHEN RIGHT(MINOR, 3) = '616' THEN REV_USD - CST_USD ELSE 0 END) '616',
        SUM(CASE WHEN RIGHT(MINOR, 3) = '641' THEN REV_USD - CST_USD ELSE 0 END) '641'
FROM [table A]
WHERE LCTRYNUM = '834'
        AND MAJOR IN ('538', '548')
        AND AMT_TYPE = 'I'
GROUP BY PRODMGR
ORDER BY PRODMGR

See also

http://www.sqlservercentral.com/foru...ssageid=296604
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69501

for complex queries like this you may want to select the desired data into a temp table then sum the temp tables data.