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

    Unanswered: Count Records Help

    Hello I have the following table below. I need to create a query that will list conference, avg. attendance, avg. winning percentage for the current year grouped by conference. For winning percentage I'm assuming I would need to count occurrnces of self score > opp_score then divide that by counting the number of dates entries within that year? ..Dont have a clue on how to express this query - Thanks for any help or suggestions

    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));

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Although I dont have the exact answer are you trying to do the following

    CONF ATT SELF_SCORE OPP_SCORE
    ---------- ---------- ---------- ----------
    A 5000 3 8
    A 9500 2 1
    A 6700 5 2
    B 12000 9 6
    B 8000 2 2
    B 16000 3 7

    select conf, count(*) / (select count(*) from table)
    from table
    where self_score > opp_score
    group by conf;

    This query doesn't work because you need to group the divided query into the same group as the outer query. I would also like to know how this is done.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Greetings,

    I now have the answer ,

    conf = conference

    select s1.conf, avg_att As "Avg. Att", (wins / total) * 100 As "% Won"
    from
    (select conf, count(*) As wins
    from help
    where self_score > opp_score
    group by conf) s1,
    (select conf, avg(att) As avg_att, count(*) As total
    from help
    group by conf) s2
    where s1.conf = s2.conf;

    The average attendance is for the entire conference group regardless of whether or not they won. If you only want average attendance for the games they played then select the avg(att) in the top from-query as opposed to the second.

    The sample table I used for this is shown below,

    SQL> select * from help;

    CONF ATT SELF_SCORE OPP_SCORE
    ---------- ---------- ---------- ----------
    A 5000 3 8
    A 9500 2 1
    A 6700 5 0
    B 12000 4 6
    B 8000 2 0
    B 16000 3 7

    And the results generated from the answer query

    CONF Avg. Att % Won
    ---------- ---------- ----------
    A 7066.66667 66.6666667
    B 12000 33.3333333

    Cheers.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Sep 2003
    Posts
    14
    - Thanks for your reply, It appears I left some info out, Ive been trying to adapt what you replied with but still no dice.

    I am including the tables and data below. There are some null values as I am only inputing sample data that is needed in the reports.

    CREATE TABLE SCHOOL
    (school VARCHAR2(30),
    conference VARCHAR2(25),
    stadium_size NUMBER(6),
    ticket_price NUMBER(4,2),
    in_state_players NUMBER(2),
    outstate_players NUMBER(2),
    scholarships NUMBER(2),
    graduate NUMBER(20));

    INSERT INTO school VALUES
    ('Indiana Univ.', 'Big Ten', 53000, null, null, null, null, null);
    INSERT INTO school VALUES
    ('Ohio State Univ.', 'Big Ten', 104000, null, null, null, null, null);
    INSERT INTO school VALUES
    ('Penn State Univ.', 'Independent', 80000, null, null, null, null, null);
    INSERT INTO school VALUES
    ('Univ. of Pittsburgh', 'Independent', 51000, null, null, null, null, null);
    INSERT INTO school VALUES
    ('Pondunk Univ.', 'Independent', 44000, null, null, null, null, 35);
    INSERT INTO school VALUES
    ('Violator Univ.', 'Independent', 39000, null, null, null, null, 47);

    CREATE TABLE SCHOOL_INCIDENTS
    (school VARCHAR2(30),
    idate DATE,
    incident_code NUMBER(5));

    INSERT INTO school_incidents VALUES
    ('Indiana Univ.',null, 17983);
    INSERT INTO school_incidents VALUES
    ('Ohio State Univ',null, 12891);
    INSERT INTO school_incidents VALUES
    ('Penn State Univ.',null, 17250);

    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, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 38000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 37000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Indiana Univ.', null, null, 36000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 51000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 50000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 49000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 48000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 47000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 46000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 45000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 44000, 7, 0, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 43000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 42000, 0, 7, null, null);
    INSERT INTO homegame VALUES
    ('Penn State Univ.', null, null, 41000, 0, 7, null, null);

    Below is the basic query I have been working from. I will need to add a column inbetween conference and attendance for "WINNNING PERCENTAGE" which would be calcuated for self_score & opp_score in homegame
    then average the attendance column and count the recruiting incidents per conference. The final output should be 2 rows listing the calculated data.

    SELECT ALL SCHOOL.CONFERENCE, HOMEGAME.ATTENDANCE "AVG ATTENDANCE", SCHOOL_INCIDENTS.INCIDENT_CODE "RECRUITING INCIDENTS"
    FROM SCHOOL, SCHOOL_INCIDENTS, HOMEGAME
    WHERE ((SCHOOL.SCHOOL = HOMEGAME.SCHOOL)
    AND (HOMEGAME.SCHOOL = SCHOOL_INCIDENTS.SCHOOL));

    CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
    ------------------------- -------------- --------------------
    Big Ten 46000 17983
    Big Ten 45000 17983
    Big Ten 44000 17983
    Big Ten 43000 17983
    Big Ten 42000 17983
    Big Ten 41000 17983
    Big Ten 40000 17983
    Big Ten 39000 17983
    Big Ten 38000 17983
    Big Ten 37000 17983
    Big Ten 36000 17983

    CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
    ------------------------- -------------- --------------------
    Independent 51000 17250
    Independent 50000 17250
    Independent 49000 17250
    Independent 48000 17250
    Independent 47000 17250
    Independent 46000 17250
    Independent 45000 17250
    Independent 44000 17250
    Independent 43000 17250
    Independent 42000 17250
    Independent 41000 17250

Posting Permissions

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