From: esquel@sommarskog.se   
      
   Anton Shepelev (anton.txt@g{oogle}mail.com) writes:   
   > 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   
   >   
      
   If you are on SQL 2022, you can use the new function   
   APPROXIMATIVE_PERCENTILE_CONT which is a regular aggregate function.   
   But as the name says, it is approximative. It's intended for large   
   data sets where exact calculation would take a lot of time. I don't   
   know how well it works on smaller data sets.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)