Isaiah Melendez
asked on
Query Help - MSSQL - Averages
Hello, Experts,
I have a query written that does an average from a column via a 52-week period. The manipulation here is dividing a count of a column called RO_NO by 52 to give me the total number of records then dividing by the number of weeks. However, I ran into something a bit weird with the results.
When I run the data I get a result on one record for the total of a number of RO_NO's in the giving time frame to be 16. So when I do the formula to divide 16/52 instead of 0.3076923077 I get 0.
Why is this happening? And how can I fix it?
I have a query written that does an average from a column via a 52-week period. The manipulation here is dividing a count of a column called RO_NO by 52 to give me the total number of records then dividing by the number of weeks. However, I ran into something a bit weird with the results.
When I run the data I get a result on one record for the total of a number of RO_NO's in the giving time frame to be 16. So when I do the formula to divide 16/52 instead of 0.3076923077 I get 0.
Why is this happening? And how can I fix it?
COUNT(RO_NO) / 52 as [Avg Per Year]ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was the solution to my question, thanks, Scott P.!
ASKER
ROUND(COUNT(RO_NO) / 52.0, 2) as [Avg Per Year]This rounds. Duh! I should have tried that first before asking. :)
And to limit the number of decimals in the display to the rounded number, CAST it with that number of decimals:
CAST(ROUND(COUNT(RO_NO) / 52.0, 2) AS decimal(9, 2)) AS [Avg Per Year]
CAST(ROUND(COUNT(RO_NO) / 52.0, 2) AS decimal(9, 2)) AS [Avg Per Year]
ASKER
The rounding is very long. To shorten it to lets say XX.XX would the ROUND function be appropriate here even though the count function is being used? Of so, how would it be displayed?