setting sum to a numeric type in SQL statement

[ifntech]

I'm subtotalling in SQL Server in a query and then post that to a graph.
The graph is saying it is not a numeric value.
What is the sql code to set the "sum(value) as totalvalue" to a numeric data type.

Thanks Dan

 

[SQLDenis]

What is it now? an int?
select convert(numeric(12,3),100)
this will make 100 a decimal with a 3 decimals precision

100.000

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ifntech]

sum(value) as totalvalue
Value is the database field in the group by
Unknown what is the output type just using group by sql

convert(numeric(SUM(table.field),100)

Gave me an incorrect sytax near sum error.

 

[SQLDenis]

you need precision and scale like this

convert(numeric(12,4),SUM(table.field))

example

create table #test (id int)
insert into #test
select 1 union all
select 2 union all
select 3 union all
select 4 


select convert(numeric(12,4),SUM(id))
from #test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ifntech]

convert(numeric(12,2),SUM(fieldname)) AS Number1

I got this to run

but when I try to calc off of two of them.

<% comp=((Number1)/(Number2))%>
<%=comp%>

I get a Microsoft VBScript runtime error '800a000d'
Type mismatch
error

 

[SQLDenis]

what datatype is Number2 and also what datatype is comp

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ifntech]

Number 2 is identical just a diffent field

comp is just the code above comp = num1/num2 in the gui

 

[SQLDenis]

is code a Currency datatype?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ifntech]

decimal in the db for both.

the group by function seems to sum fine and generate data.
But when I try to use the data it seems like it sees it as a nvarchar or something and I get a type mismatch in the straight division calc

and an error of non-numeric value when I try to graph it.

 

[SQLDenis]

I meant to say is comp a currency datatype?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ifntech]

It isn't any type right now it is just the output of the calc.

I've never had to convert the output and I think it is stalling at the division with the type mismatch.

I'll try to convert it and see if that works.

 

[ifntech]

No luck