3 Replies Latest reply: Nov 20, 2012 11:52 AM by user13179060 RSS

    without using analytic formula

    user13328581
      dear experts;

      I have data in the following form below
      id    req_Date                    errand1  errand2       j1score    j2score
      1     9/10/2012            1           null                60          null
      1     9/10/2012            1           null                null          61
      2     9/11/2012            1            1                  40          null
      3     9/12/2012            null         1                   30         30
      this is the results i need below
      id    req_Date                    Totalerrands     j1score    j2score    avg_score
      1     9/10/2012                    1              60           61       60.5   
      2     9/11/2012                    2              40           null      40
      3     9/12/2012                    1              30           30       30
      see sample sql statement below
      create table t1
      (
      id number(30),
      req_date   Date,
      errand1  number(30),
      errand2  number(30),
       j1score  number(30),
       j2score number(30)
      );
      
      
      insert into table t1 values (1, to_date('9/10/2012', 'MM/DD/YYYY'), 1, null, 60, null);
      insert into table t1 values (1, to_date('9/10/2012', 'MM/DD/YYYY'), 1, null, null, 61);
      insert into table t1 values (1, to_date('9/11/2012', 'MM/DD/YYYY'), 1, 1, 40, null);
      insert into table t1 values (1, to_date('9/12/2012', 'MM/DD/YYYY'), null, 1, 30, 30);
      All help appreciated. THank you
        • 1. Re: without using analytic formula
          Frank Kulash
          Hi,
          user13328581 wrote:
          dear experts;

          I have data in the following form below
          id    req_Date                    errand1  errand2       j1score    j2score
          1     9/10/2012            1           null                60          null
          1     9/10/2012            1           null                null          61
          2     9/11/2012            1            1                  40          null
          3     9/12/2012            null         1                   30         30
          In the INSERT statements below, id is always 1. Which is correct?
          this is the results i need below
          id    req_Date                    Totalerrands     j1score    j2score    avg_score
          1     9/10/2012                    1              60           61       60.5   
          2     9/11/2012                    2              40           null      40
          3     9/12/2012                    1              30           30       30
          Always explain how you get the results you want. There are several different reasons why you might want these same results from this (or any) small set of sample data, but would produce different results given your real data.
          see sample sql statement below
          create table t1
          (
          id number(30),
          req_date   Date,
          errand1  number(30),
          errand2  number(30),
          j1score  number(30),
          j2score number(30)
          );
          
          
          insert into table t1 values (1, to_date('9/10/2012', 'MM/DD/YYYY'), 1, null, 60, null);
          insert into table t1 values (1, to_date('9/10/2012', 'MM/DD/YYYY'), 1, null, null, 61);
          insert into table t1 values (1, to_date('9/11/2012', 'MM/DD/YYYY'), 1, 1, 40, null);
          insert into table t1 values (1, to_date('9/12/2012', 'MM/DD/YYYY'), null, 1, 30, 30);
          Thanks for posting the CREATE TABLE and INSERT statements. Remember why you do to all that trouble: So the people who want to help you can re-create the problem and test their ideas. If you post statements that don't work, then it doesn't help as much. None of the INPUT statements above help; they all have an extra word "table" before the table name. Please test (and, if necessary, correct) your statements before you post them. Do you want people to post answers that work? Show the same courtesy yourself.
          All help appreciated. THank you
          See the froum FAQ {message:id=9360002}

           

          Here's one way to get the results you requested:
          SELECT       id, req_date
          ,       COUNT (DISTINCT errand1)
                   + COUNT (DISTINCT errand2)     AS totalerrands
          ,       MAX (j1score)                 AS j1score
          ,       MAX (j2score)               AS j2score
          ,       SUM ( NVL (j1score, 0) 
                     + NVL (j2score, 0)
                     )
               / NULLIF ( COUNT (j1score) + COUNT (j2score)
                       , 0
                     )               AS avg_score
          FROM      t1
          GROUP BY  id, req_date
          ORDER BY  id, req_date
          ;
          • 2. Re: without using analytic formula
            Solomon Yakobson
            select  req_date,
                    nvl(max(errand1) ,0)+ nvl(max(errand2),0) totalerrands,
                    sum(j1score) j1score,
                    sum(j2score) j1score,
                    (nvl(sum(j1score),0) + nvl(sum(j2score),0)) / (count(j1score) + count(j2score)) avg_score
              from  t1
              group by req_date
              order by req_date
            /
            
            REQ_DATE  TOTALERRANDS    J1SCORE    J1SCORE  AVG_SCORE
            --------- ------------ ---------- ---------- ----------
            10-SEP-12            1         60         61       60.5
            11-SEP-12            2         40                    40
            12-SEP-12            1         30         30         30
            
            SQL> 
            SY.
            • 3. Re: without using analytic formula
              user13179060
              SELECT ID, req_date,
              MAX (NVL (errand1, 0)) + MAX (NVL (errand2, 0)) toterrands,
              MAX (NVL (j1score, 0)) l, MAX (j2score) ma,
              CASE
              WHEN MAX (j1score) IS NULL OR MAX (j2score) IS NULL
              THEN MAX (NVL (j1score, j2score))
              ELSE (MAX (NVL (j1score, 0)) + MAX (NVL (j2score, 0))) / 2
              END l
              FROM t2
              GROUP BY ID, req_date