Just wondering if I have data like

 name  score
    A    2
    B    5
    C    7

I would like to have a 3rd row

     name  score  total
        A    2     14
        B    5     14
        C    7     14

I mean ultimately -- I want to find 'z-scores' or percentiles of various row entries across various totals. I'm just not sure if there's a simple way to compare row values to aggregate values, except by a subquery such as

select A.name, A.score, B.total
from myTable A
left join
(select name, sum(score) from myTable group by name) B
on A.name = B.name

That could work but it seems inefficient querying the same table twice for what is a calculation. Is there any simpler way of doing it that I'm missing?


Yes, there is a way. It's called "window aggregate functions":

    A.name, A.score, 
    sum(A.score) over () as total
from myTable A ;

The OVER () means that the aggregate (sum in this case) is over the whole result set. If you wanted the total over say different names (assuming you had rows with same name, you could use OVER (PARTITION BY name).

  • Thanks -- yes I used rank()over (partition by) all the time -- of course rank isn't really an aggregation but perhaps it somewhat is as it's group-looking. But this is exactly what I needed, thanks!
    – user45867
    Nov 28 '18 at 15:54
  • Yeah. I suppose "window functions" is a better name for ROW_NUMBER / RANK / DENSE_RANK functions. Nov 28 '18 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.