SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Average SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aboyse
Starting Member

12 Posts

Posted - 07/12/2013 :  04:51:39  Show Profile  Reply with Quote
How do you get the average number in sql?

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  04:55:09  Show Profile  Reply with Quote
use AVG() function

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 07/12/2013 :  05:50:12  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  06:03:29  Show Profile  Reply with Quote
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
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 07/12/2013 :  06:07:54  Show Profile  Reply with Quote
Sorry still returned a 0 even tried to change the 100 to 200
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  06:16:16  Show Profile  Reply with Quote
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
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 07/12/2013 :  06:20:50  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  06:23:19  Show Profile  Reply with Quote
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
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 07/12/2013 :  06:24:39  Show Profile  Reply with Quote
yes it gave me the sum of 0 and it counted 10 rows of info: not exactly what I want...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  06:28:39  Show Profile  Reply with Quote
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
Go to Top of Page

aboyse
Starting Member

12 Posts

Posted - 07/12/2013 :  06:32:52  Show Profile  Reply with Quote
got it just had to change the values from money to normal numbers.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/12/2013 :  06:38:49  Show Profile  Reply with Quote
ok..cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000