CODEWITHSUNDEEP
sqloracle-database
the Internet
the Internet

Reputation: 98

Calculating a conditional cumulative sum in Oracle

The broad view of what I'm trying to do is find out how many reservations that have not yet happened yet are on the books in the entire system, by the date that the reservation was booked. This means counting the number of all records that exist with a redemption_date after or equal to booking_date, grouping by booking_date. Please see the following hypothetical example for a better explanation: 

redemption_date      booking_date
2013-01-01           2013-01-01
2013-01-06           2013-01-01
2013-01-06           2013-01-01
2013-01-07           2013-01-02
2013-01-08           2013-01-03
2013-01-09           2013-01-04
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-10           2013-01-05
2013-01-11           2013-01-05

I would like the result:

booking_date         number_of_reservations
2013-01-01           3
2013-01-02           3
2013-01-03           4
2013-01-04           5
2013-01-05           11

But my brain is completely failing me as to how the query should be structured. Any tips? Thanks!

Edit: To clarify, number_of_reservations should be the number of reservations that were booked on that date, as well as those booked on days AFTER that. In other words, number_of_reservations is the number of reservations that are in the database as of booking_date (that have not yet happened). My original results did have mistakes. Sorry for the confusion

Upvotes: 4

Views: 35455

Answers (5)

Ram
Ram

Reputation: 1

Here is a simple solution.

select a.trans_id,
  a.quantity+nvl((select sum(quantity) from table1 where trans_id<a.trans_id),0) quantity 
from table1 a 
order by a.sys_trans_id

Upvotes: -2

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

SELECT
    booking_date,
    COUNT(
        CASE WHEN redemption_date >= booking_date 
        THEN 1 END
    ) AS number_of_reservations
FROM
    Reservations
GROUP BY
    booking_date

Sql Fiddle

Edit:

Based on the updated description, I believe this should give the desired results:

SELECT DISTINCT
    r."booking_date",
    (SELECT COUNT(*) 
     FROM reservations r2 
     WHERE 
         r2."booking_date" <= r."booking_date"
         AND r2."redemption_date" >= r."booking_date"
    ) AS number_of_reservations
FROM
    Reservations r
ORDER BY r."booking_date"

Sql Fiddle

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this readily by using case with a sum.

select booking_date,
       SUM(case when redemption_date >= booking_date then 1 else 0 end)
from bookings b
group by booking_date
order by booking_date

By the way, this isn't really a cumulative sum. The would have the syntax:

sum(whatever) over (partition by . . . order by . . . )

It would put a calculated value on every row.

Upvotes: 4

Matt Busche
Matt Busche

Reputation: 14333

this should get you what you're looking for

SELECT booking_date, SUM(CASE WHEN booking_date >= redemption_date THEN 1 ELSE 0 END) AS number_of_reservations
FROM yourtable
GROUP BY booking_date
ORDER BY booking_date

Upvotes: 2

Joe
Joe

Reputation: 1388

try this (I am assuming that you want all of them, not ones that appear more than a certain number)

select booking_date, count(*) as number_of_reservations
from table_name
group by booking_date order by booking_date

Upvotes: 1

Related Questions