SQLTeam.com | Weblogs | Forums

Basic addition of two columns


#1

I have a query with fields like these:

SELECT (SELECT Sum (ABS (Quantity)) FROM Table1 WHERE Type= '1' and Code = ‘ X’ ) As Field1

,(SELECT Sum (ABS (Quantity)) FROM Table1 WHERE Type= '2' and Code= ‘ Y’ ) As Field 2

The query works correctly that far!

Now I want a field that is the addition of Field1 and Field2

I have tried , ( convert( int, Field1) + convert (int, Field2) ) As Field3

But that does not work!!

Is it because the Field1 and Field2 are not real fields? and if not, how do I do basic math with fields created with the AS syntax?

Appreciate help!
Eva


#2

Note that the aliases are not available until after the SELECT clause is processed.

Here's one way:

 select sum(field1) field1, sum(field2) field2, sum(field1)+ sum(field2) field3
 from 
 (
 	 select '1' gp,
		 case when type = '1' and code = 'x' then quantity end as field1
	   , case when type = '2' and code = 'y' then quantity end as field2
	 from table1 t1
) s
group by gp

#3

Ah!!
Thank you so much!!