Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Unanswered: Query Average Help

    Hello I have the following table and data. I need to find the avg game attendance for homegame (where shcool = 'Indiana Univ.' and away games ( where opponent = 'Indiana Univ." This would be 3 columns listing the SCHOOL 'Indiana Univ.", AVG HOMEGAME ATTENDANCE, AVG AWAY ATTENDANCE. I have no clue how to format the query to get the last column.

    - Thanks for your help and sugestions.

    CREATE TABLE HOMEGAME
    (school VARCHAR2(30),
    hdate DATE,
    opponent VARCHAR2(30),
    attendance NUMBER(6),
    self_score NUMBER(3),
    opp_score NUMBER(3),
    self_injuries NUMBER(3),
    opp_injuries NUMBER(3));

    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 46000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 45000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 44000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 43000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 42000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 41000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 40000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 39000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 38000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 37000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 36000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 51000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 50000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 49000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 48000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 47000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 46000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 45000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 44000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 43000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 42000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, 'Indiana Univ.', 41000, 0, 7, null, null);

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore,India
    Posts
    51

    Re: Query Average Help

    select 1,avg(goals) from table where it is home
    union
    select 2,avg(goals) from table where it is not home

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    UNION is nice, but it returns 2 rows

    Try

    SELECT 'Indiana Univ.' as school,
    avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
    avg(case opponent when 'Indiana Univ.' then attendance else null end) AS avg_awaygame
    from homegame;

    It returns :

    SCHOOL AVG_HOMEGAME AVG_AWAYGAME
    Indiana Univ. 41000 46000

  4. #4
    Join Date
    Sep 2003
    Posts
    14
    - Thanks for the Reply. Never used case before. I have tried moving the parethesis aronund bet keep getting the belower error?

    SQL> SELECT 'Indiana Univ.' as school,
    2 avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
    3 avg(case opponent when 'Indiana Univ.' then attendance else null end) AS avg_awaygame
    4 from homegame;
    avg(case school when 'Indiana Univ.' then attendance else null end) AS avg_homegame,
    *
    ERROR at line 2:
    ORA-00907: missing right parenthesis


    SQL>

  5. #5
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    I guess you are using an Oracle version that doesn't support CASE (e.g. 8.1.6)

    Instead, you can use the good old DECODE.

    This does not work :
    SQL> SELECT (CASE 1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END) FROM DUAL;
    SELECT (CASE 1 WHEN 1 THEN 'TRUE' ELSE 'FALSE' END) FROM DUAL
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis

    But this might work :
    SQL> SELECT DECODE(1,1,'TRUE','FALSE') FROM DUAL;

    DECO
    ----
    TRUE

    So, in your case :
    SELECT 'Indiana Univ.' as school,
    avg(decode(school,'Indiana Univ.',attendance,null)) AS avg_homegame,
    avg(decode(opponent,'Indiana Univ.',attendance,null)) AS avg_awaygame
    FROM homegame;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •