Calculating median in the same query

Question

I have a table with a column of floats. All the time that I need a median (or first quartile, for example) I have to compute it first and in other query I compute average, total sum, number of records, etc... (all together).

My question is: Is there a way to compute median, average, total sum,... in the same query?

Actually I think the queries were too dirty. It was a lot of code just to find the median. If the query has a lot of clauses in the WHERE, I would need to use WITH to simplify, etc... As you can see, it exists a better solution for this (but only works in SQL Server 2012) — Nizam
– Nizam, Commented Feb 7, 2014 at 5:19
Gordon Linoff suggested an elegant solution that works for SQL SERVER 2005 and upwards. In this solution (also in the one I suggested), you have all the filtering in just one query. The answer given by Jorge Campos requires a WITH or you will need to repeat all filtering to subquery a, b and x. — Nizam
– Nizam, Commented Feb 7, 2014 at 5:54

Nizam · Accepted Answer · 2014-02-07 05:38:03Z

1

You can do the following in SQL Server 2005 and upwards:

SELECT AVG(Salary) as AVERAGE,
       MAX(case when seqnum = cnt / 2 then salary end) as median,
       MAX(SALARY) as MAXIMUM, 
       MIN(SALARY) as MINIMUM,
       SUM(SALARY) as TOTAL,
       TOTAL as NUMBER_OF_EMP
FROM (SELECT e.*, 
             count(*) over () as total,
             row_number() over (order by salary) as seqnum
      FROM TblEmployees e
     ) e

edited Feb 7, 2014 at 5:38

Nizam

4,7193 gold badges45 silver badges63 bronze badges

answered Feb 7, 2014 at 5:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nizam Over a year ago

I didn't know that. This is far more elegant than have two different queries . Mainly when you have a lot of filtering in it. More than that, it is much more readable.

Jorge Campos · Accepted Answer · 2014-02-07 03:12:46Z

0

Just use a select to the two select as sub-selects

select * from
     (select max(id) mx, min(id) mi, avg(id) av, 
             sum(id) sm
        from test) a,
(select id as median from
    (select id, 
           row_number() over (order by id) rownumber
    from test) x
where rownumber = (select count(*) from test) / 2) b

See it here on fiddle: http://sqlfiddle.com/#!3/b9d60/6

answered Feb 7, 2014 at 3:12

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

2 Comments

Nizam Over a year ago

Not good enough. If I need to calculate the median, and first and third quartile, I would need three subqueries to the same data. It is encapsulated the same query but is is still different queries. This case I have 3 subqueries to achieve the result I want (which could be very expensive if test is huge)

Jorge Campos Over a year ago

I think there is no way to do what you want because median is a specific case that you need to rollout all data and then find the middle position. Putting that along with an aggregate data... I think this is just impossible.

Nizam · Accepted Answer · 2014-02-07 05:16:57Z

I have found this function in SQL Server 2012 that does what I want:

http://technet.microsoft.com/en-us/library/hh231327.aspx

I couldn't solve the problem in just one query but it is more clean than the option I had before.

Look this functional example: SQLFIDDLE

Supose I have a table of remuneration of employees, I could use the function as:

SELECT 
   AVG(Salary) MEAN,
   MAX(MEDIAN) MEDIAN,
   MAX(SALARY) MAXIMUM,
   MIN(SALARY) MINIMUM,
   SUM(SALARY) TOTAL,
   COUNT(*)    NUMBER_OF_EMP
FROM (
    SELECT *, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER() MEDIAN 
    FROM TblEmployees
) A

Collectives™ on Stack Overflow

Calculating median in the same query

3 Answers 3

1 Comment

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Related