How to make a SUM without group by

Question

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.

`(sum(auction)-actual)` doesn't make sense. Should probably be `actual - sum(auction)`. — Erwin Brandstetter, Oct 09 '13 at 16:44

score 26 · Accepted Answer · answered Oct 09 '13 at 12:18

26

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

answered Oct 09 '13 at 12:18

Idan Arye

12,402
5
49
68

Thank you so much... i want ask also that, how to deal with this problem in SQL? – Aijaz Chauhan Oct 09 '13 at 12:30
1

SQL Server also has this feature, with the same syntax: http://technet.microsoft.com/en-us/library/ms189461.aspx – Idan Arye Oct 09 '13 at 12:32
1

I think it should be `actual - Sum(Auction) OVER (PARTITION BY id) AS Remaining` – ypercubeᵀᴹ Oct 09 '13 at 13:55
Hello! If I execute: SELECT sum(x) FROM data The result is: "ERROR: column "x" must appear in the GROUP BY clause or be used in an aggregate function"... Instead if y execute: SELECT sum(x) as my_label FROM data Works fine.... :S I am using postgresql... Does the SOLUTION is add an alias??? – Gabriel Oct 22 '20 at 18:48
@Gabriel - this is not really related to the original question - OP asked about using an aggregate together with a individual rows, while you are asking about a general error you get trying to using a simple aggregate. This deserves its own question (or maybe even a ticket in the PostgreSQL issue tracker, since it does look like a bug...) – Idan Arye Oct 25 '20 at 18:20
AWSOME! I have looked up tons of pages and this is the proper answer. Thank you. – sogu Feb 23 '22 at 12:32

score 21 · Answer 2 · answered Apr 23 '15 at 18:42

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.

How to make a SUM without group by

2 Answers2

Linked