| Author |
Topic |
|
mattboy_slim
Yak Posting Veteran
72 Posts |
 Posted - 2013-08-01 : 18:02:02
|
| I want to get the "Average Session Length" as a decimal value instead of an integer.Returns an integer:SUM(f_sessionlength)/60/60)/COUNT(*) as 'Average Session Length'Returns an error:CAST((SUM(f_sessionlength)/60/60)/COUNT(*)) as 'Average Session Length' - Error: Incorrect syntax near 'CAST', expected 'AS'.I'm obviously doing this incorrectly. Can anyone shed any light on my problem?Thanks in advance,Matt |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-01 : 18:10:24
|
SUM(f_sessionlength) / 3600E / COUNT(*)
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-08-01 : 18:25:57
|
| Thanks! What function does the "E" serve?Also, as a follow-up, how can you limit it two a fixed number of decimals (if necessary)? I don't necessarily need to do that in this case, but since I'm learning, I just as well try to soak up a little more knowledge. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-01 : 20:16:42
|
| E is the scientific notation for floating point numbers (example 3.7e2 which is 370).Adding an E at the end of 3600 forces it to floating point data type, which in turn forces the divisions to be floating point type, thus preserving the fractional parts (as opposed to integer division, which will truncate the fractional parts).You can use 3600E, 3600E0, 3600., 3600.0 or anything else that signals to SQL Server that it is a floating point number. |
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-01 : 20:38:13
|
quote:
Originally posted by mattboy_slim
Also, as a follow-up, how can you limit it two a fixed number of decimals (if necessary)? I don't necessarily need to do that in this case, but since I'm learning, I just as well try to soak up a little more knowledge.
You can use CAST(), like this:[CODE]CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2))[/CODE] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-02 : 01:37:49
|
| http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-08-02 : 10:12:05
|
| Thanks everyone, this has been very helpful. |
|
|
|
sivadss2007
Starting Member
18 Posts |
Posted - 2013-08-28 : 11:58:54
|
| CAST(((SUM(f_sessionlength)/60/60)/COUNT(*)) as numeric(18,9)) as 'Average Session Length'P.Siva |
|
|
|
|
Problems using CAST