Sum help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sum help

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-17 : 06:01:59

Hi,

got abit of a data issue, but need to get sums off few figures on the tables I have.

cust_id, grading
001, 065
001, A
001, 067
002, abc
002, 115

I tried case(sum(grading) > 0) then sum(grading) else grading, but I get grading is not in group clause etc.

Any tips on parsing strings etc?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 06:10:05

select sum(grading)as sumn,grading into sampletable from urtable group by grading

select case when sumn > 5 then sumn else grading end from sampletable

take the sum in another table
try this once

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-17 : 06:13:00

Ok I can take the grading into a sample table but how would i eliminate the characters and just leave the integers?

quote:
Originally posted by bklr

select sum(grading)as sumn,grading into sampletable from urtable group by grading

select case when sumn > 5 then sumn else grading end from sampletable

take the sum in another table
try this once

umertahir
Posting Yak Master

154 Posts

Posted - 2008-12-17 : 06:20:02

Can't you try ISNUMERIC() function to check if the number is integer only then do the sum?

quote:
Originally posted by cipriani1984

Ok I can take the grading into a sample table but how would i eliminate the characters and just leave the integers?

quote:
Originally posted by bklr

select sum(grading)as sumn,grading into sampletable from urtable group by grading

select case when sumn > 5 then sumn else grading end from sampletable

take the sum in another table
try this once

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:23:48

quote:
Originally posted by umertahir

Can't you try ISNUMERIC() function to check if the number is integer only then do the sum?

quote:
Originally posted by cipriani1984

Ok I can take the grading into a sample table but how would i eliminate the characters and just leave the integers?

quote:
Originally posted by bklr

select sum(grading)as sumn,grading into sampletable from urtable group by grading

select case when sumn > 5 then sumn else grading end from sampletable

take the sum in another table
try this once

ISNUMERIC is not always reliable try this

SELECT ISNUMERIC('3e2'),ISNUMERIC('4d5')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:27:12

try this instead

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-17 : 06:28:11

I tried

SUM(ISNUMERIC(grading)) and it gave me really low figures that dont add up correctly?

quote:
Originally posted by umertahir

Can't you try ISNUMERIC() function to check if the number is integer only then do the sum?

quote:
Originally posted by cipriani1984

Ok I can take the grading into a sample table but how would i eliminate the characters and just leave the integers?

quote:
Originally posted by bklr

select sum(grading)as sumn,grading into sampletable from urtable group by grading

select case when sumn > 5 then sumn else grading end from sampletable

take the sum in another table
try this once

umertahir
Posting Yak Master

154 Posts

Posted - 2008-12-17 : 06:31:03

You would need to add CASE statement using ISNUMERIC

quote:

ISNUMERIC is not always reliable try this
SELECT ISNUMERIC('3e2'),ISNUMERIC('4d5')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:36:07

quote:
Originally posted by umertahir

You would need to add CASE statement using ISNUMERIC
quote:

ISNUMERIC is not always reliable try this
SELECT ISNUMERIC('3e2'),ISNUMERIC('4d5')

?? what do mean by that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:37:29

quote:
Originally posted by cipriani1984

I tried

SUM(ISNUMERIC(grading)) and it gave me really low figures that dont add up correctly?

it should be

SUM(CASE WHEN ISNUMERIC(grading)=1 AND CHARINDEX('d', grading)=0 AND CHARINDEX('e', grading)=0  THEN grading ELSE 0 END)

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-17 : 06:38:22

I was confused by that also,

I did

select distinct cust_id, sum(grading) as grade
where ISNUMERIC(grading) > 0

but I get really low 1 digit values when the values in the tables are 3 digits.

Any ideas?

quote:
Originally posted by visakh16

quote:
Originally posted by umertahir

You would need to add CASE statement using ISNUMERIC
quote:

ISNUMERIC is not always reliable try this
SELECT ISNUMERIC('3e2'),ISNUMERIC('4d5')

?? what do mean by that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:45:22

quote:
Originally posted by cipriani1984

I was confused by that also,

I did

select distinct cust_id, sum(grading) as grade
where ISNUMERIC(grading) > 0

but I get really low 1 digit values when the values in the tables are 3 digits.

Any ideas?

what does last suggestion give u?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-17 : 06:55:55

worked, thanks

quote:
Originally posted by visakh16

quote:
Originally posted by cipriani1984

I was confused by that also,

I did

select distinct cust_id, sum(grading) as grade
where ISNUMERIC(grading) > 0

but I get really low 1 digit values when the values in the tables are 3 digits.

Any ideas?

what does last suggestion give u?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:57:51

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources