Window Function avg is not displaying decimals ?

Question

Window Function avg is not displaying decimals ?

protocoder

Old Hand

Points: 374
More actions
April 26, 2022 at 8:51 am
Go to Answer
#4022322
Hi Experts,
I am not able to understand, why window average function are not showing decimal part.
Avg of 1.92 is rounding off to 1.00 and the below code is showing 1 and if I cast and round then it shows 1.0
```
,AVG(ad.[No of PurchaseReq])  over (Order by ad.Purchase_DateROWS BETWEEN 6 PRECEDING AND CURRENT ROW) avg7Days
,AVG(ad.[No of PurchaseReq])  over (Order by ad.Purchase_DateROWS BETWEEN 29 PRECEDING AND CURRENT ROW) avg30days --This is rolling not fixed 30 days
,avg(ad.[No of PurchaseReq]) over(partition by ad.addMonth) AS avgaddenDateMonth
,avg(ad.[No of PurchaseReq])  over (Partition by ad.Department) avgdept
```
Is this some settings or I am doing something terribly wrong.
2. How can I use the Rolling Average to display for Financial Month and Financial Week data?
Please your kind help is appreciated.
Phil Parkin

SSC Guru

Points: 246962
More actions
April 26, 2022 at 9:07 am
Answer
#4022331
Perhaps this example explains what is happening?
```
SELECT AVG(x.n)
FROM (VALUES (1), (2), (3), (5)) x (n);

SELECT AVG(CAST(x.n AS DECIMAL(19, 6)))
FROM (VALUES (1), (2), (3), (5)) x (n);
```
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

For question 2, you likely want a windowed AVG with an ORDER BY clause in the PARTITION BY. Without sample data, or expected results, it's difficult to know.

For question one, as Phil has alluded to, an aggregate function like AVG returns the same data type that was passed to it. Presumably [No of PurchaseReq] is an int (how do you have half a purchase?) and so the value returned is also an int. You would need to CAST/CONVERT the value first, before you AVG it.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

protocoder Old Hand Points: 374 More actions · Answer 2

Thank you @Phil Parkin @thom-2 you very much both of you. I have tested and able to get right avg for Data Type money and was able to convert to decimal and get avg.

2. Thank you @thom-2 for help in explanation. I have already converted the dates to FY and FQuarters, so I will use them to Order By and test once I am back in office tomorrow.

Thanks a ton.