Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

HestonJames (Programmer) (OP)
11 Nov 08 5:52
Hello Guys,

This will be a simple enough question I'm sure, I suspect its caused by datatype mismatches of some kind between int's and float's or something, maybe you can help?

I have a simple query which looks like this:

CODE

Select    Max(message_count.messagecount) As Highest,
        Min(message_count.messagecount) As Lowest,
        Avg(message_count.messagecount) As Average
From (
    Select        MessageLog.Device_ID,
                Count(Distinct MessageLog.Media_ID) AS MessageCount
    From        MessageLog
    Group By    MessageLog.Device_ID
    ) As message_count

This query runs without error however, the AVG() returned seems to be rounded down, rather than a proper decimal placed average of the column values that I really want.

So for instance, example result sets might look like this:

Highest: 2
Lowest:  1
Average: 1

or

Highest: 4
Lowest:  1
Average: 1

Now, am I using Avg() correctly here? or should I be using another method?

At the moment I'm able to solve the issue by doing something like this: Where I cast the value as a float instead of what I'm guessing is an Int.

CODE

Select    Max(message_count.messagecount) As Highest,
        Min(message_count.messagecount) As Lowest,
        Avg(cast(message_count.messagecount as float)) As Average
From (
    Select        MessageLog.Device_ID,
                Count(Distinct MessageLog.Media_ID) AS MessageCount
    From        MessageLog
    Group By    MessageLog.Device_ID
    ) As message_count

I'm just interested to see if this is the correct way of handling it, or is there is a better built in method to be using?

Cheers all,

Heston
Helpful Member!  r937 (TechnicalUser)
11 Nov 08 7:16
you could also have used

   Avg(1.0*message_count.messagecount) As Average

smile
 

r937.com | rudy.ca

HestonJames (Programmer) (OP)
11 Nov 08 8:49
Hey r937,

Thanks for that, I'm guessing that multiplying by the float forces the cast to happen automagicaly? is that correct?

Cheers,

Heston
Helpful Member!  gmmastros (Programmer)
11 Nov 08 9:03
Yes.

In my opinion, it's "no better/no worse" than your solution.  

In your code, you did an explicit conversion to float.  Rudy's code causes an explicit data type conversion.  Personally, I prefer the explicit method (cast/convert).  For me, it's more obvious (when you read the code) what is actually happening.  You could also argue that the implicit method (multiply by 1.0) is less code.  Less code isn't necessarily important, but it allows you to more easily focus on the other important aspects of the code.  In this case, it's important to realize that you are getting an average.  It's less important that you need to convert to float in order to get an accurate average.

It's worth knowing the order in which SQL Server does it's implicit (auto-magical) conversions.  Take a look here.
http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx

Quote:

user-defined data types (highest)
1. sql_variant
2. xml
3. datetime
4. smalldatetime
5. float
6. real
7. decimal
8. money
9. smallmoney
10. bigint
11. int
12. smallint
13. tinyint
14. bit
15. ntext
16. text
17. image
18. timestamp
19. uniqueidentifier
20. nvarchar (including nvarchar(max) )
21. nchar
22. varchar (including varchar(max) )
23. char
24. varbinary (including varbinary(max) )
25. binary (lowest)

Not all data types can be converted to other data types, but when then can, and SQL Server needs to do this for you auto-magically, this list is what it uses to determine the resulting data type.

For example... 1.0 * 20

1.0 is float
20 is int

According to the list, float is #5, int is #11.  When combining these data types, SQL Server will convert to float because it has a higher precedent on the list.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

HestonJames (Programmer) (OP)
11 Nov 08 9:13
George,

That is very interesting! I've added a copy of that datatype list to my notepad here to keep it in mind.

It's nice to know there isn't any real benefit to either I or r937's approach apart from personal preference.

Cheers to you both for this, very helpful!

Heston
r937 (TechnicalUser)
11 Nov 08 9:15

Quote (george):

1.0 is float
interesting

i'd've thought it would be decimal

smile
 

r937.com | rudy.ca

gmmastros (Programmer)
11 Nov 08 9:39
Rudy,

You're right.

CODE

Select SQL_VARIANT_PROPERTY(1.0, 'BaseType') As [Base Type],
       SQL_VARIANT_PROPERTY(1.0, 'Precision') As [Precision],
       SQL_VARIANT_PROPERTY(1.0, 'Scale') As [Scale]

Output:

Base Type   Precision   Scale
---------   ---------   -----
numeric     2           1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close