3 Replies Latest reply: Jun 16, 2014 8:09 AM by Frank Kulash RSS

    Aggregate on a subset of records

    Diwakar Dayalan

      Hi All,

       

      Below is my requirement - Oracle 11G. For the list of account and branch i have a transaction type and cost type. In the output of this query i need to get medical pay and reserve pay calculated for that account. Example If the transaction type = medical and Cost type is reserve sum up the amount for the medical pay, if they are not of above types put zero. If the transaction type is claimcost and Cost type is reserve sum up the amount  and populate it in reserve pay.

       

      Input is as below

       

      Account #Branch NumberExposureStatusAmountTransaction TypeCost Type
      CB32747499414393018125377open226.3medicalReserve
      CB32747499414393018125386closed45medicalReserve
      CB32747499414393018125386closed45medicalReserve
      CB32747499414393018125405closed20claimcostReserve
      CB32747499414393018125405closed-9.66claimcostReserve
      CB32747499414393018125425open0claimcostReserve
      CB32747499414393018125495open30.7medicalReserve
      CB35936078415718156128161open0claimcostPayment
      CB35936078415718156128161open0claimcostPayment
      CB35936078415718156128161open0claimcostReserve
      CB35936078415718156128161open0claimcostReserve

       

      Output:

       

      Account #Branch NumberExposureStatusAmountTransaction TypeCost TypeMedical  PayReserve Pay
      CB32747499414393018125377open226.3medicalReserve3470
      CB32747499414393018125386closed45medicalReserve3470
      CB32747499414393018125386closed45medicalReserve3470
      CB32747499414393018125405closed20claimcostReserve010.34
      CB32747499414393018125405closed-9.66claimcostReserve010.34
      CB32747499414393018125425open0claimcostReserve010.34
      CB32747499414393018125495open30.7medicalReserve3470
      CB35936078415718156128161open0claimcostPayment010.34
      CB35936078415718156128161open0claimcostPayment010.34
      CB35936078415718156128161open0claimcostReserve010.34
      CB35936078415718156128161open0claimcostReserve010.34

       

       

      I am using the case statement as below  getting medical pay. But the sum occurs the account number and does not calculate for subset. Any suggestions on getting this aggregation corrected.

       

      (CASE WHEN ((UPPER(COSTYPE)='RESERVE') AND UPPER(TXNTYPE)='MEDICAL'))

      THEN SUM (AMOUNT)  OVER (PARTITION BY ACCTNUMBER,TXNTYPE,COSTYPE) ELSE NULL END)

        • 1. Re: Aggregate on a subset of records
          Mike Kutz

          You need to put the CASE statement inside the SUM().

          BTW - you'll lose the order so make sure you add ORDER BY

           

          MK

           

          with data(Account#,    Branch#,    Exposure,    Status,    Amount,    "Transaction Type",    "Cost Type")
          as (
          select 'CB327474994',    14393018,    125377,    'open',    226.3,    'medical',    'Reserve' from dual union all
          select 'CB327474994',    14393018,    125386,    'closed',    45,    'medical',    'Reserve' from dual union all
          select 'CB327474994',    14393018,    125386,    'closed',    45,    'medical',    'Reserve' from dual union all
          select 'CB327474994',    14393018,    125405,    'closed',    20,    'claimcost',    'Reserve' from dual union all
          select 'CB327474994',    14393018,    125405,    'closed',    -9.66,    'claimcost',    'Reserve' from dual union all
          select 'CB327474994',    14393018,    125425,    'open',    0,    'claimcost',    'Reserve' from dual union all
          select 'CB327474994',    14393018,    125495,    'open',    30.7,    'medical',    'Reserve' from dual union all
          select 'CB359360784',    15718156,    128161,    'open',    0,    'claimcost',    'Payment' from dual union all
          select 'CB359360784',    15718156,    128161,    'open',    0,    'claimcost',    'Payment' from dual union all
          select 'CB359360784',    15718156,    128161,    'open',    0,    'claimcost',    'Reserve' from dual union all
          select 'CB359360784',    15718156,    128161,    'open',    0,    'claimcost',    'Reserve' from dual
          )
          select D.*
            ,sum( CASE
                when "Transaction Type"='medical'
                  and "Cost Type"='Reserve' then amount
                else 0
                end
              )
              over (partition by account#,branch#,"Transaction Type","Cost Type")
            as medical_pay
          from data D
          order by account#,branch#,exposure;
          
          • 2. Re: Aggregate on a subset of records
            Diwakar Dayalan

            Thank you Mike. It worked In the below set of records i need to get the count of exposures (distinct) i have on the branch.

             

            COUNT (EXPOSURE)  OVER (PARTITION BY branch, EXPOSURE) AS EXPSR_CNT   -> This statement does seem to work but it gives the total number of records rather than distinct exposures. If i add a distinct then the count goes back to 1.

             

            Account #Branch NumberExposureStatusAmountTransaction TypeCost Type
            CB32747499414393018125377open226.3medicalReserve
            CB32747499414393018125386closed45medicalReserve
            CB32747499414393018125386closed45medicalReserve
            CB32747499414393018125405closed20claimcostReserve
            • 3. Re: Aggregate on a subset of records
              Frank Kulash

              Hi,

               

               

              Diwakar Dayalan wrote:

               

              Thank you Mike. It worked In the below set of records i need to get the count of exposures (distinct) i have on the branch.

               

              COUNT (EXPOSURE)  OVER (PARTITION BY branch, EXPOSURE) AS EXPSR_CNT   -> This statement does seem to work but it gives the total number of records rather than distinct exposures. If i add a distinct then the count goes back to 1.

               

              Account # Branch Number Exposure Status Amount Transaction Type Cost Type
              CB327474994 14393018 125377 open 226.3 medical Reserve
              CB327474994 14393018 125386 closed 45 medical Reserve
              CB327474994 14393018 125386 closed 45 medical Reserve
              CB327474994 14393018 125405 closed 20 claimcost Reserve

              You don't want exposure in the PARTITION BY clause.

              "PARTITION BY branch, exposure"  means you want a separate total for each distinct combination of branch and exposure.  If all the rows in the partition have the same branch and eposure, then they will certainly all have the same exposure, so COUNT (DISTINCT exposure) will be 1.

               

              I hope this answers your question.

              If not, please post CREATE TABLE and INSERT statements for the sample data, and also post the results you want from that data.

              Explain, using specific examples, how you get those results from that data.

              Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

              See the forum FAQ: https://forums.oracle.com/message/9362002#9362002