CODEWITHSUNDEEP
sqlsql-server
user2136748
user2136748

Reputation: 65

SQL STDEV on a calculate column

So I have 2 columns I want to do a division on then I want to do a stdev on the resulting column I was trying the following sql but it doesnt work I havent been able to figure out how to save the internal select as a variable and then just do stdev on that var. Am I barking up the wrong tree? any ideas? 

select 
stdev(
  select 
       convert(decimal(20,5),convert(decimal(20,5),(s.received))
        /convert(decimal(20,5),(s.sent)))*100 as DDR 
        from someTable s 
        where s.SomethingName = 'thisthingsName' 
        and s.Date like '2015-04-16%'
)
from someTable

Upvotes: 1

Views: 138

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

select 
    stdev(
        convert(decimal(20,5),convert(decimal(20,5),(s.received))
        / convert(decimal(20,5),(s.sent)))*100  
    ) as deviation
from 
    someTable s
where
    s.SomethingName = 'thisthingsName' 
    and s.Date like '2015-04-16%'

Please note if your version of SQL Server is 2008 or higher, you can also just do 

and cast(s.Date as date) = '2015-04-16'

and in SQL Server 2012 you can do 

and cast(s.Date as date) = DATEFROMPARTS(2015,04,16)

rather than a (non-exact!) string comparison on a date field.

Upvotes: 1

Related Questions