How to use a SQL window function to calculate a percentage of an aggregate

I think you are looking for this:

SELECT d1, d2, sum(v)/sum(sum(v)) OVER (PARTITION BY d1) AS shareFROM   testGROUP  BY d1, d2;

Produces the requested result.

Window functions are applied after aggregate functions. The outer sum() in sum(sum(v)) OVER ... is a window function (attached OVER ... clause) while the inner sum() is an aggregate function.

Effectively the same as:

WITH x AS (   SELECT d1, d2, sum(v) AS sv   FROM   test   GROUP  BY d1, d2   )SELECT d1, d2, sv/sum(sv) OVER (PARTITION BY d1) AS shareFROM   x;

Or (without CTE):

SELECT d1, d2, sv/sum(sv) OVER (PARTITION BY d1) AS shareFROM  (   SELECT d1, d2, sum(v) AS sv   FROM   test   GROUP  BY d1, d2   ) x;

Or @Mu's variant.

Aside: Greenplum introduced correlated subqueries with version 4.2. See release notes.

Do you need to do it all with window functions? Sounds like you just need to group the result you have by d1 and d2 and then sum the sums:

select d1, d2, sum(p)from (    select d1, d2, v/sum(v) over (partition by d1) as p    from test) as dtgroup by d1, d2

That gives me this:

 d1 | d2 |          sum           ----+----+------------------------ a  | x  | 0.25000000000000000000 a  | y  | 0.75000000000000000000 b  | x  | 1.00000000000000000000