CODEWITHSUNDEEP
sqloracle-database
lukasz_k
lukasz_k

Reputation: 23

Running Total in Oracle SQL - insert missing rows

Let's assume I have following set of data in Oracle SQL database:

Product  Year  Month  Revenue    
A        2016  1      7
A        2016  5      15

After creating running totals with following code

select Product, Year, Month, Revenue,
       sum(Revenue) over (partition by Product, Year order by Month) Revenue_Running
  from exemplary_table

I receive following result:

Product  Year  Month  Revenue  Revenue_Running
A        2016  1      7        7
A        2016  5      15       22

Is there any way that I can get this:

Product  Year  Month  Revenue  Revenue_Running
A        2016  1      7        7
A        2016  2      (null)   7
A        2016  2      (null)   7
A        2016  4      (null)   7
A        2016  5      15       22

Upvotes: 2

Views: 264

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You need a calendar table and Left join with your exemplary_table

SELECT p.product, 
       c.year, 
       c.month, 
       COALESCE(revenue, 0), 
       Sum(revenue)OVER (partition BY p.product, c.year  ORDER BY c.month) Revenue_Running 
FROM   calendar_table c 
       CROSS JOIN (SELECT DISTINCT product 
                   FROM   exemplary_table) p 
       LEFT JOIN exemplary_table e 
              ON c.year = e.year 
                 AND e.month = c.month 
WHERE  c.dates >= --your start date
       AND c.dates <= --your end date

Upvotes: 1

Related Questions