SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Median Function

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Median Function
Author Message
jerry



Joined: 31 Oct 2002
Posts: 3

Post Median Function Reply with quote

Is there a SQL median function like average

Thu Oct 31, 2002 4:12 pm View user's profile Send private message
jerry



Joined: 31 Oct 2002
Posts: 3

Post Re: Median Function Reply with quote

: Which database?

SQL Server

so T-SQL

Thu Oct 31, 2002 4:13 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Re: Median Function Reply with quote

This is taken from Joe Celko's "SQL FOR SMARTIES" book, second edition:

23.2.8 Celko's Third Median

... lots of stuff explaining things ..

SELECT AVG(DISTINCT weight)
FROM (SELECT P1.weight

FROM Parts AS P1, Parts AS P2

GROUP BY P1.pno, P1.weight

HAVING SUM(CASE WHEN P2.weight = P1.weight

THEN 1 ELSE 0 END)

>= ABS(SUM(CASE WHEN P2.weight P1.weight THEN -1

ELSE 0 END)))

AS Partitions;

If you prefer to use functions instead of a CASE expression, then use
this version of the query:

SELECT AVG(DISTINCT weight)
FROM (SELECT P1.weight

FROM Parts AS P1, Parts AS P2

GROUP BY P1.pno, P1.weight

HAVING SUM(ABS(1 - SIGN(P1.weight - P2.weight))

>= ABS(SUM(SIGN (P1.weight - P2.weight)))

AS Partitions;

Thu Oct 31, 2002 4:39 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Re: Median Function Reply with quote

No, not in T-SQL. Please see my answer for your previous question.

By the way, this is not a forum to post general SQL questions. Please use this forum for questions related to DB Tools, DB Mail and DB Audit software.

: Is there a SQL median function like average

Thu Oct 31, 2002 4:41 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.