Geek Answers Handbook

Calculating Conditional Total Amount in Oracle

A broad look at what I'm trying to do is to find out how many more reservations have not yet been made, but in books throughout the system, by the reservation date. This means counting the number of all records that exist with redemption_date after or equal to booking_date , grouped by booking_date . For a better explanation, see the following hypothetical example:

 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 need 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 completely fails in how the query should be structured. Any tips? Thanks!

Edit: To clarify, number_of_reservations should be the number of reservations that were booked at this date, as well as those that were ordered on the days after that. In other words, number_of_reservations is the number of reservations that are in the database, starting with booking_date (which have not yet occurred). My initial results had errors. Sorry for the confusion

+4
source share
5 answers
 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 that 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

+11
source

You can do this with case with 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 is actually not a cumulative amount. The syntax will have the following syntax:

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

He would put a calculated value on each line.

+4
source

this should get what you are 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
+2
source

try this (I assume you want all of them, not the ones that were displayed more than a certain number)

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

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
-2
source

More articles:


All Articles