CODEWITHSUNDEEP
sqldatabaseoracle-database
Kifayat Ullah
Kifayat Ullah

Reputation: 619

how to sum up database field value based on date in oracle

I have a table having two fields in it just like below given.

enter image description here

How to create a view that will sum TOT_HITS field's value till each date appeared in corresponding row in TODAY column like given below.

enter image description here

Upvotes: 0

Views: 92

Answers (2)

MT0
MT0

Reputation: 167981

Use an analytic function to perform the query with only a single table scan:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE your_table( today, tot_hits ) As
SELECT DATE '2018-01-16', 5498 FROM DUAL UNION ALL
SELECT DATE '2018-01-17', 4235 FROM DUAL;

Query 1:

SELECT t.*,
       SUM( tot_hits ) OVER ( ORDER BY today ) AS tot_hits_to_date
FROM   your_table t

Results:

|                TODAY | TOT_HITS | TOT_HITS_TO_DATE |
|----------------------|----------|------------------|
| 2018-01-16T00:00:00Z |     5498 |             5498 |
| 2018-01-17T00:00:00Z |     4235 |             9733 |

Upvotes: 3

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Just Try This

SELECT
    Today,
    Hits,
    TillDate = Hits+NVL((SELECT SUM(Hits) FROM YourTable  WHERE Today < T.Today),0)
    FROM YourTable T

Upvotes: 1

Related Questions