CODEWITHSUNDEEP
sqloracle-database
AFJ
AFJ

Reputation: 159

SQL - Sum Data Field To Date

I can't figure out how to sum a data field to date...

My query current outputs something similar to this:

Date    Count
12/1    3
12/2    5
12/3    2

I would like to add a column that sums up the count column to-date:

Date    Count    To-Date
12/1    3        3
12/2    5        8
12/3    2        10

Any ideas?

***Update - The table i'm trying to do this for is a little more complex:

ID  Date    Count for Date
X   10/11/11    10  
X   10/11/11    10  
X   14/11/11    2   
X   14/11/11    2   
X   22/11/11    21  
X   23/11/11    50
X   23/11/11    50

Using This code:

Sum(Count for Date) over (partition by x, date order by date rows between unbounded preceding and current row)

I end up getting:

10
20
2
4
21
50
100

Upvotes: 0

Views: 2368

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

Assuming that you want the total for each distinct ID to be distinct, you'd want something like 

SELECT id, 
       date_column, 
       count_for_date, 
       sum(count_for_date) over (partition by id order by date_column) running_total
  FROM some_table_name

If you want to ignore the ID and sum all rows by the date_column, just remove the partition by in the analytic function

SELECT id, 
       date_column, 
       count_for_date, 
       sum(count_for_date) over (order by date_column) running_total
  FROM some_table_name

Upvotes: 0

Glenn
Glenn

Reputation: 9150

[Updated to reflect the change, I'm guessing a bit on what the result should be, I assume you want to add up for the day? If not, can you also show your expected result?]

An analytic function would do this:

CREATE TABLE test(id int, count int);

INSERT INTO test VALUES(10, 10);
INSERT INTO test VALUES(10, 10);
INSERT INTO test VALUES(14, 2);
INSERT INTO test VALUES(14, 2);
INSERT INTO test VALUES(22, 21);
INSERT INTO test VALUES(23, 50);
INSERT INTO test VALUES(23, 50);

SELECT id, SUM(count) AS sum_for_day
  FROM test
  GROUP BY id
  ORDER BY id;

 id | sum_for_day
----+-------------
 10 |          20
 14 |           4
 22 |          21
 23 |         100
(4 rows)

SELECT id, sum_for_day
      ,sum(sum_for_day) over (order by id rows between unbounded preceding and current row) AS to_date
  FROM ( SELECT id, SUM(count) AS sum_for_day
       FROM test
           GROUP BY id
           ORDER BY id) x;

 id | sum_for_day | to_date
----+-------------+---------
 10 |          20 |      20
 14 |           4 |      24
 22 |          21 |      45
 23 |         100 |     145
(4 rows)

Upvotes: 4

Related Questions