CODEWITHSUNDEEP
sqlsql-servert-sql
cocoo84hh
cocoo84hh

Reputation: 321

Rounding within avg() over () clause not working - SQL Server

Does anybody know how to rewrite this 7-day moving average query in SQL Server, so that I get the value rounded 2 decimal places after the comma?

Current query:

SELECT round(avg(amount) OVER (
            ORDER BY DATE ASC rows BETWEEN 6 preceding
                    AND CURRENT row
            ), 2) average_amount

Current output:

120.00
121.00
120.00

Desired output:

120.11
121.18
120.20

What am I missing here?

Upvotes: 1

Views: 1258

Answers (1)

zip
zip

Reputation: 4061

Run it like this:

Select round(avg(amount*1.0) over (order by date asc ROWS BETWEEN 6 PRECEDING AND current row),2) average_amount

Sometimes it just needs *1.0

Upvotes: 3

Related Questions