3 Replies Latest reply on Jun 6, 2014 3:26 PM by Shatag

    Analytic SQL

    ricard888

        CREATE TABLE CUSTOMER

         ( CUSTOMER_ID NUMBER (10),

        CLASS VARCHAR2(10)

         )  ;

       

       

      Insert into CUSTOMER (CUSTOMER_ID,CLASS) values ('210000','PREM');

      Insert into CUSTOMER (CUSTOMER_ID,CLASS) values ('210001','WO');

      Insert into CUSTOMER (CUSTOMER_ID,CLASS) values ('210002','HIGH');

      Insert into CUSTOMER (CUSTOMER_ID,CLASS) values ('210003','HIGH');

      COMMIT;

       

       

        CREATE TABLE ACCOUNT

         ( CUSTOMER_ID NUMBER(10),

            ACCOUNT_ID NUMBER(10),

              BALANCE NUMBER(10)

         );

       

       

      Insert into ACCOUNT (CUSTOMER_ID,ACCOUNT_ID,BALANCE) values ('210003','110005','-450.0');

      Insert into ACCOUNT (CUSTOMER_ID,ACCOUNT_ID,BALANCE) values ('210000','110000','-200.0');

      Insert into ACCOUNT (CUSTOMER_ID,ACCOUNT_ID,BALANCE) values ('210000','110001','300.0');

      Insert into ACCOUNT (CUSTOMER_ID,ACCOUNT_ID,BALANCE) values ('210001','110002','3000.0');

      Insert into ACCOUNT (CUSTOMER_ID,ACCOUNT_ID,BALANCE) values ('210002','110003','-405.0');

      Insert into ACCOUNT (CUSTOMER_ID,ACCOUNT_ID,BALANCE) values ('210002','110004','805.0');

      COMMIT;

       

      I want to achieve the output as the following where account balance is in negative (overpayment) I want and as well the same customer whose others account in positive. and sum balance by customer.

      These the example I mocked up  in the output in excel.

       

      CUSTOMER_ID ACCOUNT_ID BALANCE CLASS CUSTOMER_TOTAL
      210000 110000 -200 PREM 100
      210000 110001 300 PREM 100
      210002 110003 -405 HIGH 400
      210002 110004 805 HIGH 400
      210003 110005 -450 HIGH -450
        • 1. Re: Analytic SQL
          chris227

          select

            c.CUSTOMER_ID

          ,ACCOUNT_ID

          ,BALANCE

          ,CLASS

          ,sum(balance) over (partition by c.CUSTOMER_ID)

            CUSTOMER_TOTAL

          from customer c, account a

          where

          c.customer_id = a.customer_id

          and

          c.customer_id in (

          select

          customer_id

          from account

          where

          balance < 0)

          order by

            c.CUSTOMER_ID

          ,ACCOUNT_ID

           

          Message was edited by: chris227 sorry, didnt see that it was two tables. join added

          • 2. Re: Analytic SQL
            user8987724

            Try this..

             

            SELECT ac.customer_id,

                   ac.account_id,

                   SUM (ac.balance) OVER (PARTITION BY ac.customer_id, ac.account_id) balance,

                   c.class,

                   SUM (ac.balance) OVER (PARTITION BY ac.customer_id) customer_total

              FROM account ac, customer c

            WHERE ac.customer_id = c.customer_id;

            • 3. Re: Analytic SQL
              Shatag

              Hi ,

               

              here below the query, you will get output exactly as required...

               

              SELECT a.customer_id,

                              b.account_id,

                              b.balance,

                              b.class,

                              SUM(b.balance) OVER (PARTITION BY a.customer_id) customer_total

              FROM  customer a, account b

              WHERE a.customer_id = b.customer_id

              ORDER BY a.customer_id, b.account_id ASC.

               

              Shatag..