From: anton.txt@g{oogle}mail.com   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)