SQL column of sums of multiple rows while keeping all rows?-CodePudding

So I have a query select that outputs something like this: (the actual results I'm working with is much more complicated but these are the important parts)

I want to create a result like this:

id   trans  total
123   5.00   5.00
124   6.00  13.00
124   7.00  13.00
125   8.00  17.00
125   9.00  17.00

Basically I want to add a column that contains a total of all the transactions for each id, while still showing all the transactions. I think the solution will have something to do with group by, nested selects and the sum function but I can't get it right.

CodePudding user response：

A windowed function works well for this scenario:

select
  *
  ,sum(trans) over(partition by id) as total
from
  myTable

SqlFiddle Example

CodePudding user response：

Generally speaking, you want:

SUM(value) OVER (PARTITION BY group)

If the first output is from this:

SELECT id, SUM(col) AS trans
FROM table
GROUP BY id;

Then you need this:

SELECT id, SUM(col) AS trans, SUM(SUM(col)) OVER (PARTITION BY id) AS id_total
FROM table
GROUP BY id;

If the first output is from this:

SELECT id, trans
FROM table;

Then you need this:

SELECT id, trans, SUM(trans) OVER (PARTITION BY id) AS id_total
FROM table;