SQL Math Functions

1. Senior Member
Join Date
Dec 1969
Posts
431

## SQL Math Functions

I am using some simple math functions in my SQL statement to get the average of a coumn, but I can&#039;t seem to get it to return decimal places. I&#039;ve used SELECT AVG(coumn), SELECT (SUM(solumn)/COUNT(column)), SELECT ROUND((SUM(solumn)/COUNT(column)),2)....and every time I get an integer. I&#039;ve tried using Round() in ASP around the AVG() value in the recordset, but the value is an integer in the record set, so there are no digits after the decimal. I&#039;ve done the math manually and these are definitely not dividing without remainders so I am not sure what I am doing wrong???<BR><BR>Thanks!

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Maybe you should read the docs?

And you don&#039;t say *WHICH* database, either.<BR><BR>Tell us which DB and I can give you some hints.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
2,885

## RE: SQL Math Functions

Try CASTing it to a Decimal:<BR><BR>CAST(AVG(Column) AS Decimal)

4. Senior Member
Join Date
Dec 1969
Posts
2,885

## RE: BTW.. For SQL Server [eop]

.

5. Senior Member
Join Date
Dec 1969
Posts
2,885

## RE: ooops...

Had it backwards. Try:<BR>AVG(CAST(Column AS decimal))

6. Senior Member
Join Date
Dec 1969
Posts
11,247

## No

A Decimal MUST have precision and scale!<BR>So<BR><BR>CAST(AVG(Column) AS Decimal(10, 5))

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## I don't think that works...

...if the column is integer, you&#039;ll get an integer average and then cast the integer to decimal. So you&#039;ll always have zero after decimal point.<BR><BR>I think you need to cast it *before* it gets averaged:<BR><BR>AVG(CAST(Column AS Real))<BR><BR>And unless you have an abiding need for decimal, I&#039;d use REAL, since then you get hardware-speed for the arithmetic instead of the much much slower software decimal arithmetic.<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
2,885

## RE: No

I just ran it and it worked without it. "MUST", no. "SHOULD", probably.

9. Senior Member
Join Date
Dec 1969
Posts
2,885

## RE: See "ooops" [eom]

:)

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## LOL...okay, I'm slow but right...

...it&#039;s a bad idea to cast to Decimal, if you don&#039;t really *NEED* to.<BR><BR>And I can&#039;t imagine he does. He&#039;s taking the average of a bunch of integers. Heck, the REAL calculation could well be more accurate. Not to mention much faster.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•