Combine SUM of two columns into one

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

General SQL Server Forums

New to SQL Server Programming

Combine SUM of two columns into one

Author

Topic

GarryR
Starting Member

1 Post

Posted - 2007-11-09 : 12:04:52

Hi,

I have a query that looks at the stock levels in one warehouse and returns the quantity, have been asked to create a new column that shows the total of the same stock that is available in our two other warehouses.

Have tried this:
SELECT ItemCode, WhsCode, InStock FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.WhsCode = TABLE1.WhsCode WHERE WhsCode = '31' or WhsCode = '61' GROUP BY InStock, WhsCode,ItemCode

This returns the results in one column rather than in a seperate column for 31 & 61, I then need to add the two columns together so a total stock is shown.

I realise this may be a basic query but I'm batting my head against a wall at the moment.

Thanks
Garry

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 12:21:07

probably the most simple way:


select quantity1, quantity2, quantity1+quantity2 as quantityFull
from
(
select (your select for quantity1 here) as quantity1, 
       (your select for quantity2 here) as quantity2
) t

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 13:24:12

quote:
Originally posted by GarryR

SELECT ItemCode, WhsCode, InStock FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.WhsCode = TABLE1.WhsCode WHERE WhsCode = '31' or WhsCode = '61' GROUP BY InStock, WhsCode,ItemCode

How are you joining?
Please provide expected output.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 13:29:08

Try this alternative

SELECT		t1.ItemCode,
		SUM(CASE WHEN t2.WhsCode = '31' THEN t2.InStock ELSE 0 END) AS Code31,
		SUM(CASE WHEN t2.WhsCode = '61' THEN t2.InStock ELSE 0 END) AS Code61
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.WhsCode = t1.WhsCode
WHERE		t2.WhsCode IN ('31', '61')
GROUP BY	t1.ItemCode

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources