Calculations including decimal places

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 2000 Forums

Transact-SQL (2000)

Calculations including decimal places

Author

Topic

billsack
Starting Member

35 Posts

Posted - 2008-06-19 : 11:23:16

Hello experts,

I cannot work this one out. Muyst be the stupidest question ever!!

I want the answer to show two decimal places...

Select SUM(Field1) from Table1 / Select COUNT(*) from Table1

ie 9952 / 27461 = 2.75

I keep just getting 2 despite everything I try including cast etc. Can someone please help as this is doing my head in!

Cheers

Billsack

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-19 : 11:28:08

[code]
Select (SUM(Field1) from Table1 ) / (Select COUNT(*) * 1.0 from Table1)
[/code]

Chirag

http://www.chirikworld.com

billsack
Starting Member

35 Posts

Posted - 2008-06-19 : 11:34:41

Just get an error!!!!!!

billsack
Starting Member

35 Posts

Posted - 2008-06-19 : 11:37:00

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

billsack
Starting Member

35 Posts

Posted - 2008-06-19 : 11:43:44

Ok thanks very much Chirag!!!!!!

Changed a bit and it works!

select (Select SUM(Field1) from Table1 ) / (Select COUNT(*) * 1.0 from Table1)

Whoop da whoop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 11:44:13

quote:
Originally posted by billsack

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Select SUM(Field1) /(COUNT(*) * 1.0)  from Table1

billsack
Starting Member

35 Posts

Posted - 2008-06-19 : 12:02:31

Ok thanks guys. How about calculating this from a bunch of records in a table not the sum and counts ie...

Field1/Field2 = Result

Field1 Field2 Result
2222 250 8.88

Flumoxed again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:08:32

quote:
Originally posted by billsack

Ok thanks guys. How about calculating this from a bunch of records in a table not the sum and counts ie...

Field1/Field2 = Result

Field1 Field2 Result
2222 250 8.88

Flumoxed again!

SELECT (Field1 * 1.0)/Field2 FROM Table

billsack
Starting Member

35 Posts

Posted - 2008-06-20 : 05:23:02

Cheers for that. It works a treat.

Thanks ever so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:57:58

Also understand why you need to do this:-

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources