Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2240

Calculating the percentile

Show key headers only | View raw

Hello, all (which is how many?)

I am trying to solve a trivial problem, and my solution
turns out unexpectedly cumbersome and likeky suboptimal.
Given a table storing values (val) over dimension (dim), I
want to query the total, mean and median of the value over
the dimension.  My solution is:

  SELECT
     dim         ,
     SUM(val) tot,
     AVG(val) avg,
     med
  FROM
  (  SELECT
        dim,
        val,
        PERCENTILE_CONT( 0.5 )
        WITHIN GROUP (ORDER BY val)
        OVER (PARTITION BY dim) AS med
     FROM #test
  ) w_med
  GROUP BY dim, med

I had to wrap the percentile calculation into a subquery
(w_med) because PERCENTILE_CONT() does not seem to work like
a typical aggregate function, accepting the dimension in a
separate (and mandatory) OVER clause. The subquery then must
be GROUPed not only by the dimension, but also by the
calculated median value, which is redundant because there is
only one median for each dimension. Is there a better way?

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Calculating the percentile Anton Shepelev <anton.txt@g{oogle}mail.com> - 2024-03-11 14:42 +0300
  Re: Calculating the percentile Erland Sommarskog <esquel@sommarskog.se> - 2024-03-14 14:55 +0100

csiph-web