Getting "Null" values for Aggregating the Data

Question

I writing a query and getting the value

I have tried this on SQL Server 2012

;With CTE_ColorIndicator
AS
(
    select Name,Value,Report_Date,
    Dense_Rank() Over(Order by Report_Date DESC)Dno,
    (AVG(value)+(3*STDEV(value))) UpperLimit,
    (AVG(value)+(3*STDEV(value)))*0.85 Limit 
    from #Temp1 
    group by Name,value,Report_date
)

Select * 
from CTE_ColorIndicator 
where Dno <=90 and Dno > 7

"I expected the aggregated values in the place of Upper-limit and limit columns" "But the output is showing 'NULL' values in the upper-limit and limit columns"

Yes STDEV(value) is null how to overcome this... i mean how to get STDEV aggregated values. — venugopal, May 27 '19 at 06:47
Purpose of using DENSE_RANK is not that clear. Can you please explain with adding some sample and expected output data? — mkRabbani, May 27 '19 at 06:47
The query is aggregating the single value. Try `select stdev(10)` — Serg, May 27 '19 at 06:47
Use `COALESCE(STDEV(value), 0`. There is no deviation if `STDEV` is applied to just one value. — Giorgos Betsos, May 27 '19 at 06:48
As per https://stackoverflow.com/questions/20937087/stdev-function-returns-null-when-table-contains-only-one-row, you can't calculate STD with just one row per combination. — devC, May 27 '19 at 06:52

score 0 · Answer 1 · answered May 27 '19 at 13:05

You are grouping by value. Hence, the standard deviation is not defined. I think you intend for the subquery to be:

select Name, Report_Date,
       Dense_Rank() Over (Order by Report_Date DESC) as Dno,
       (AVG(value) + 3 * STDEV(value)) as UpperLimit,
       (AVG(value) + 3 * STDEV(value)) * 0.85 as Limit 
from #Temp1 
group by Name, Report_date

Getting "Null" values for Aggregating the Data

1 Answers1