Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Standard Deviation

0 views
Skip to first unread message

Abraham

unread,
Jan 15, 2004, 10:40:04 AM1/15/04
to
Is there any way in SQL Server to use Standard deviation as an aggregate
function.
STDDEV -- Is a numeric expression. Aggregate functions and subqueries are
not permitted

Thanks


Aaron Bertrand - MVP

unread,
Jan 15, 2004, 10:48:18 AM1/15/04
to
SELECT
STDEV(x) FROM
(
SELECT x = 1 UNION ALL SELECT x = 2
) y

Result:
0.70710678118654757


--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Abraham" <bin...@yahoo.com> wrote in message
news:OVuvZ332...@TK2MSFTNGP11.phx.gbl...

Tom Moreau

unread,
Jan 15, 2004, 10:52:53 AM1/15/04
to
The function - STDEV () - is an aggregate function.  Check it out in the BOL.

--
Tom
 
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
 
 

Steve Kass

unread,
Jan 15, 2004, 11:09:37 AM1/15/04
to
The SQL Server function is STDEV(), and it is an aggregate function, as in

SELECT type, STDEV(price)
FROM pubs..titles
GROUP BY type

STDEV is the sample standard deviation. If you need the population
standard deviation, use STDEVP instead.

SK

Louis Davidson

unread,
Jan 15, 2004, 12:15:07 PM1/15/04
to
From books online:

STDEV
Returns the statistical standard deviation of all values in the given
expression.

Syntax
STDEV ( expression )

Arguments
expression

Is a numeric expression. Aggregate functions and subqueries are not

permitted. expression is an expression of the exact numeric or approximate
numeric data type category, except for the bit data type.

This is a bit confusing. It means that you cannot include aggregates or
subqueries in the expression. Not that STDEV (or STDDEV is an alias) is not
an aggregate itself. It is listed in the section: Aggregate Functions as an
aggregate.

The initial description should probably read Aggregate function that
returns...


--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr...@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Abraham" <bin...@yahoo.com> wrote in message
news:OVuvZ332...@TK2MSFTNGP11.phx.gbl...

0 new messages