Search code examples
sqloracle-databasecountcumulative-sumrownum

Doing a Cumulative Count if in Oracle SQL

Please see attached file for reference. Lets say I have a Table CARRIER_RESPONSE, with the following fields Carrier, Shipment, Acceptance, Day

I would like to get a Cumulative Count of Acceptance per Day for each Carrier.

So far I have come to the code below:

SELECT Carrier
,Shipment
,Acceptance
,Day
,ROW_NUMBER() OVER (
                        PARTITION BY Carrier
                        , Day
                        , Acceptance
                        ORDER BY Day
                    ) AS "Cumulative Count of Acceptance per Day"
FROM CARRIER_RESPONSE

This code gives me the correct running count for the Lines with Acceptance Response = A but on the lines with Acceptance Response = D I also want to show the running count of Acceptance Response = A.

Essentially on every line I want to indicate how many times today the Carrier has accepted a Shipment. Is this possible?

Thanks,

Amir

Carrier Response:

Carrier Response

Solution

  • One way to do it - using sum with analytics clause and windowing.

    SELECT Carrier
,Shipment
,Acceptance
,Day
,sum(Case when Acceptance is not null then 1 else 0 end) OVER (
                        PARTITION BY Carrier
                        , Day
                        ORDER BY Day
                        ROWS between unbounded preceding and current row
                    ) AS "Cumulative Count of Acceptance per Day"
FROM CARRIER_RESPONSE