-1

I have the following table:

________________________
date        |  amount
________________________
01-01-2019  | 10
01-01-2019  | 10
01-01-2019  | 10
01-01-2019  | 10
02-01-2019  |  5
02-01-2019  |  5
02-01-2019  |  5
02-01-2019  |  5
03-01-2019  | 20
03-01-2019  | 20

These are mutation values by date. I would like my query to return the summed amount by date. So for 02-01-2019 I need 40 ( 4 times 10) + 20 ( 4 times 5). For 03-01-2019 I would need ( 4 times 10) + 20 ( 4 times 5) + 40 ( 2 times 20) and so on. Is this possible in one query? How do I achieve this?

My current query to get the individual mutations:

Select s.date,
  Sum(s.amount) As Sum_amount
From dbo.Financieel As s
Group By s.date
gulshan arora
  • 371
  • 1
  • 8
AndreThen
  • 1
  • 2

3 Answers3

1

You can try below -

DEMO

select dateval,
SUM(amt) OVER(ORDER BY dateval ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as amt
from
(
    SELECT 
    dateval, 
    SUM(amount) amt
FROM t2  group by dateval
)A

OUTPUT:

dateval              amt
01/01/2019 00:00:00  40
01/02/2019 00:00:00  60
01/03/2019 00:00:00  100
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

I suggest to use a window function, like this: 

select date, sum(amount) over( order by date)
from table
p-a-o-l-o
  • 9,807
  • 2
  • 22
  • 35
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

Try this below script to get your desired output-

SELECT A.date,
(SELECT SUM(amount) FROM <your_table> WHERE Date <= A.Date) C_Total
FROM <your_table> A
GROUP BY date
ORDER BY date

Output is-

date        C_Total
01-01-2019  40
02-01-2019  60
03-01-2019  100
mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • 1
    Not sure what `CTE` is in this query as it is never defined, most likely you meant to write a co-related subquery like `(SELECT SUM(amount) FROM B WHERE B.Date <= A.Date) C_Total` – Raymond Nijland Jul 19 '19 at 09:45
  • That will be table name in place of CTE. CTE remains there as I have tested the script using a CTE :) – mkRabbani Jul 19 '19 at 09:49