Here's the query:

```
SELECT name,
       group_name,
       number1, -- associated with either the name or the group_name
       number2,
       ...
       (number1 * number2) - SUM(number3) AS total_difference,
       SUM(total_difference) OVER (PARTITION BY group_name) AS grand_total
FROM t
GROUP BY name,
         group_name,
         number1,
         number2,
         ...;

```

A single group\_name may have many associated names.

total\_difference is generated for each name. My goal is for grand\_total to be the sum of the total\_difference for every name in group\_name. total\_difference is calculated for each name using number1 through numberx, some of which are related to the name and some of which are related to the group\_name. Here's an example table (ignore the math, these numbers are clearly just made up):

```
name | group_name | number1 | number2 | number3 | total_difference | grand_total
-----+------------+---------+---------+---------+------------------+-------------
Fred | A          | 3       | 67      | 2       | 10               | 30
Amy  | A          | 5       | 25      | 45      | 20               | 30
Jim  | B          | 8       | 33      | 15      | 30               | 100
Tom  | B          | 2       | 6       | 35      | 45               | 100
Al   | B          | 6       | 89      | 4       | 25               | 100
...

```

The problem is that I clearly can't reference total\_difference when I create grand\_total, because it doesn't exist yet. I also can't just replicate the math:

```
SELECT ...
       SUM((number1 * number2) - SUM(number3)) OVER (PARTITION BY group_name) AS grand_total
...

```

because then I would have two difference aggregate functions with different group by arguments (I tried nesting window functions, but I knew that was a dumb idea even before I got the error message).

I recognize that I could just make the table and perform a join afterwards, and I have absolutely no problem with doing that. However, it bothers me that I can't see this... This is more a learning experience than anything.

How can I pull off what I'm going for. Do I even need a window function? Is this even possible?

This table is solely for presentation purposes, FYI. I'm using PostgreSQL 9.3.