How to make a SUM without group by

问题:

Here is my problem..

Actual   Auction   Ammanat   id
7000     500       100       228,229
7000     100       100       228,229
7000     900       100       228,229
5000     0         0         230

I want result as given below

Actual   Auction   Ammanat   Remaining  id
7000     500       100       5550       228,229
7000     100       100       5550       228,229
7000     900       100       5550       228,229
5000     0         0         5000        230

Here, Remaining is (sum(auction)-actual).

I am using PostgreSQL. But if anyone know solution in SQL Server, it will be OK.

回答1:

You need a to use a window function - http://www.postgresql.org/docs/9.3/static/tutorial-window.html

Something like:

(Sum(Auction) OVER ()) - actual AS Remaining

回答2:

Idan is 100% correct. I want to provide an explanation of this:

(SUM (Auction) OVER ())

• OVER () creates a window including all rows from the original query.
• SUM (Auction) is a window function that calculates the sum of Auction.

Here is further explanation:

• Window is a user-specified set of rows within a query result set.

• Window Function computes a value (e.g. SUM) over all rows in the window.

• All rows are in the window because OVER() does not include PARTITION BY. With PARTITION BY the window would include a subset of rows.

From MSDN:

...the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values... if the OVER clause does not include PARTITION BY. This means that the function will be applied to all rows returned by the query.