Average SQL - 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

Average SQL

Author

Topic

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 04:51:39

How do you get the average number in sql?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 04:55:09

use AVG() function

http://msdn.microsoft.com/en-us/library/ms177677.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 05:50:12

I tried that and it didn't work. I keeps giving me back a result of 0 even though I know the average is 87.5

here is the code I'm using: SELECT AVG( price )
FROM room;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:03:29

may be because the datatype is int
try this too


SELECT AVG( price * 1.0 ) * 100
FROM room

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:07:54

Sorry still returned a 0 even tried to change the 100 to 200

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:16:16

ok what about this?


SELECT SUM( price * 1.0 ),COUNT(price)
FROM room

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:20:50

That's good but it only shows the number of rows which is 10... Doesn't give a average of the money for the rooms.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:23:19

quote:
Originally posted by aboyse

That's good but it only shows the number of rows which is 10... Doesn't give a average of the money for the rooms.

so did you get values for both SUM and COUNT?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:24:39

yes it gave me the sum of 0 and it counted 10 rows of info: not exactly what I want...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:28:39

quote:
Originally posted by aboyse

yes it gave me the sum of 0 and it counted 10 rows of info: not exactly what I want...

ok...then how will you get value for average?
if sum is 0 average will also be 0 as average is SUM/Count

My guess is you may have negative entries which might have cancelled

try this too


SELECT SUM( ABS(price) * 1.0 ),COUNT(price),AVG(ABS(price) * 1.0)
FROM room

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

aboyse
Starting Member

12 Posts

Posted - 2013-07-12 : 06:32:52

got it just had to change the values from money to normal numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-12 : 06:38:49

ok..cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources