Help! SUM a Field - SQL Server Forums

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

Help! SUM a Field

Author

Topic

Amber99
Starting Member

11 Posts

Posted - 2009-04-26 : 09:44:47

Hey!

I'm not sure why this would be needed but anyways I need to sum the contents of a field and make a new column...

Example:
Product_ID

SUM_NEW_FIELD

12345

99999

11111

22222

23458

so heres my code SUM(Product_ID) AS SUM_NEW_FIELD,

doesn't work i get a error... Operand data type varchar is invalid for sum operator.
blah

anyone know what I need to fix??

Thanks!

Amber-

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-04-26 : 13:20:17

Sum aggregates rows, so first youll need to take your string and parse it into a table. Then you can use sum.

Heres another way, you can put this into a function and call the same way youre currently calling the sum:


declare @num varchar(10)
set @num = '9999'

select	sum(len(@num) - len(REPLACE(@num, '1', ''))) * 1 +
		sum(len(@num) - len(REPLACE(@num, '2', ''))) * 2 +
		sum(len(@num) - len(REPLACE(@num, '3', ''))) * 3 +
		sum(len(@num) - len(REPLACE(@num, '4', ''))) * 4 +
		sum(len(@num) - len(REPLACE(@num, '5', ''))) * 5 +
		sum(len(@num) - len(REPLACE(@num, '6', ''))) * 6 +
		sum(len(@num) - len(REPLACE(@num, '7', ''))) * 7 +
		sum(len(@num) - len(REPLACE(@num, '8', ''))) * 8 +
		sum(len(@num) - len(REPLACE(@num, '9', ''))) * 9

Please post back and let us know how it worked!

Nathan Skerl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-26 : 15:34:32

[code]DECLARE @Sample TABLE
(
ProductID VARCHAR(12)
)

INSERT @Sample
SELECT '12345' UNION ALL
SELECT '99999' UNION ALL
SELECT '11111' UNION ALL
SELECT '22222' UNION ALL
SELECT '23458'

SELECT s.ProductID,
SUM(f.x)
FROM @Sample AS s
CROSS APPLY (
SELECT 1 * SUBSTRING(s.ProductID, Number, 1) AS x
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND LEN(s.ProductID)
) AS f
GROUP BY s.ProductID[/code]

E 12°55'05.63"
N 56°04'39.26"

Amber99
Starting Member

11 Posts

Posted - 2009-04-28 : 19:30:58

Hey! Thanks for the replies...

I don't think its as complex... because the question is for novice users like me lol + I haven't learned any of that coding..

I can pull the column model.. thats easy the hard part is to sum each line into another column im not sure why SUM won't work? I tried your code but got several errors because I think it may have to do with write access or something..

SEE BELOW: its basically a sum of the digits in the model column by each row.. Please help!! Thanks! Amber-

The Results is:

model SUM_OF_MODEL_COLUMN
1121 5
1232 8
1233 9
1260 9
1276 16
1288 19

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 00:43:48

And that's exactly what the two suggestions does!

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 02:43:33

And to work in SQL Server 2000 too,


SELECT		s.ProductID,SUM(SUBSTRING(s.ProductID, Number, 1)*1) AS x
			FROM	@sample as s inner join master..spt_values as m
			on Type = 'P'
			WHERE Number BETWEEN 1 AND LEN(s.ProductID)
GROUP BY	s.ProductID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources