SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Is it possible to SUM(@Variable)		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Commonman00
Starting Member

1 Posts
Posted - 04/17/2012 :  12:33:21  Show Profile  Reply with Quote
I am thinking of setting up a control table which has 2 columns:
- MeasureID
- MeasureName

The MeasureName corresponds to the column name of another table
and I want to loop through the control table, then inserting the various measures into a summary table

I then setup a cursor:
DECLARE iCursor as CURSOR FOR
SELECT MeasureID, MeasureName FROM ControlTable

OPEN iCursor
FETCH NEXT from iCursor into @MeasureID, @MeasureName

WHILE (@@FETCH_STATUS = 0)
BEGIN

--This is where I am having problem
select SUM(@MeasureName) from Sales

The value of @MeasureName is SalesAmt which is a column in the Sales table.
I keep getting error: Operand data type varchar is invalid for sum operator. I think that's because SQL is trying to sum the MeasureName field instead of SalesAmt.

Is it possible to pass a variable to the SUM function?
The Left(@MeasureName, 3) correctly returns 'Sal' but for some reasons SUM(@MeasureName) is not translated to SUM(SalesAmt).
Can anyone help?

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 04/17/2012 :  13:01:06  Show Profile  Reply with Quote
as i understand you're having column name as value in the field. so if you want sum() to applied over actual field then you need to use dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 04/18/2012 :  04:33:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts
Posted - 04/27/2012 :  08:53:40  Show Profile  Reply with Quote
Please post some sample data. Its hard to understand what you are trying to do without sample data.

N 28° 33' 11.93148"
E 77° 14' 33.66384"
Go to Top of Page

pphillips001
Starting Member

United Kingdom
23 Posts
Posted - 04/30/2012 :  05:08:47  Show Profile  Reply with Quote
Commonman00

As you are using a cursor, you have to treat the sum programatically.

Declare another variable before the loop ie DECLARE @MySum int

in the loop, you put something like SET @MySum = @MySum + (whatever value the cursor is adding up)

After the loop you can see it with SELECT @MySum.

Hope this makes sense.

Paul



===========================
There is no reality - only perspective.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000