-1

I have a table with 3 columns: id, date and amount, but I would like to get accumulated SUM for each date (Last column).

enter image description here

Do you have an easy solution how to add this column? I am trying with this:

SELECT date, sum(amount) as accumulated 
FROM table group by date
WHERE max(date);

Should I user OVER() for this?

Maja Jelen
  • 223
  • 5
  • 13
  • Please do not use tags that do not apply to your question. I removed the database tags as it is unclear which one you are actually using. Please add the tag of *only* the database you are actually using – John Conde Jan 11 '18 at 13:49
  • *A fortiori*, computing running totals is, in the real world, dependent on the make and model of database server. Here for MySQL: https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql – O. Jones Jan 11 '18 at 13:51
  • The syntax of your query is wrong, group by goes after the where clause. – Jorge Campos Jan 11 '18 at 13:51
  • Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer –  Jan 11 '18 at 13:57

2 Answers2

2

Use a window function to the total for each day:

SELECT date, 
       amount,
       sum(amount) over (partition by date) as accumulated 
FROM the_table;

However this will only work, if your dates all have the same time part (in Oracle a DATE column also contains a time). To make sure you ignore the time part, use trunc() to make sure all time parts are normalized to 00:00:00

SELECT date, 
       amount,
       sum(amount) over (partition by trunc(date)) as accumulated 
FROM the_table;
1

Use This:

SELECT T.ID, T.DATE, T.AMOUNT, (SELECT SUM(S.AMOUNT) FROM TABLE S WHERE S.DATE=T.DATE) ACCUMULATED
from
table T

This will give you the records from the table with a sum for all records for the date.

Ctznkane525
  • 7,297
  • 3
  • 16
  • 40
  • I have all the columns in the same table. I am trying to check this. – Maja Jelen Jan 11 '18 at 14:09
  • Thank you, but it doesn't work. I don't think you can use s.date, s.amount, from the other selectors? Also "from" is missing in front of table T. – Maja Jelen Jan 11 '18 at 14:28
  • i missed the from...its added – Ctznkane525 Jan 11 '18 at 14:34
  • Thank you, there is also a dot for t.id. I was however fixing it: SELECT transaction.id, transaction.date, transaction.amount, (SELECT SUM(rev.amount) FROM maintable rev WHERE rev.date=transaction.date) FROM maintable transaction; I hope my syntax is also fine. – Maja Jelen Jan 11 '18 at 14:38