This discussion is archived
3 Replies Latest reply: Nov 20, 2012 9:52 AM by user13179060 RSS

without using analytic formula

user13328581 Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points