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

    Lightbulb Unanswered: help in sql query

    the relations are

    MEMBERSHIP(membership_number PK, cover_type PK, start_date, end_date)
    PERSON(person_number PK, surname, initials, street_address, suburb, , membership_number)
    DOCTOR(doctor_number PK, surname, initials, street_address, suburb)
    PROVIDED-SERVICE(person_number PK, doctor_number PK, service_date PK, service_number)
    REBATE(service_number PK, cover_type PK, refund)
    SERVICE(service_number PK, service_name, service_description)


    1)What are the surnames, initials, membership-nos and cover-types of those persons who were provided less than the average number of unique services provided to persons in the month of August, 2003.

    for this question the explaination is

    you first need to calculate what is the average number of unique services provided to people in August 2003. For example, suppose you find that the occurred in August:

    person 1 was provided services 1, 2 and 3 (I.e. 3 unique services) during August
    person 2 was provided services 1,1,1 and 1 (1 unique service) during August
    person 3 was provided services 1,2,3,3 (3 unique services) during August

    then the average number of unique services is (3+1+3)/3=7/3=2.33

    the person(s) provided less than the average number of unique services (2.33)in August is therefore only person 2 in this example.


    i came up with the sql query

    select pe.surname,pe.initials,pe.membership_number,m.cove r_type
    from membership m,person pe
    where pe.membership_number=m.membership_number
    and person_number in
    (select person_number,
    count(*) as unique_service
    from provided_service
    where service_date between '01-aug-2003' and '30-aug-2003'
    group by person_number);

    it displays

    (select person_number,
    *
    ERROR at line 5:
    ORA-00913: too many values

    can anyone tell me how i can correct this one


    thanks in advance
    Last edited by hallosenthil; 10-17-03 at 03:35.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    You could try something like this:

    Code:
    
    select pe.surname
         , pe.initials
         , pe.membership_number
         , m.cover_type
      from membership m
         , person pe
     where pe.membership_number = m.membership_number
       and (
    select count(distinct service_number)
      from provided_service s
     where s.person_number = pe.person_number
       and service_date between '01-aug-2003' and '30-aug-2003')
         < (
    select AVG(cnt)
      from (
    select person_number, count(distinct service_number) cnt
      from provided_service
     where service_date between '01-aug-2003' and '30-aug-2003'
     group by person_number));
    
    (UNTESTED)

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2003
    Posts
    14
    the above query just returns all the rows from my database

    can anyone give me an idea
    please

    thanks in advance

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Wrong, the proof is in the pudding:

    SQL>Create table MEMBERSHIP
    2 (membership_number number(6)
    3 ,cover_type varchar2(3)
    4 ,start_date date
    5 ,end_date date);

    Table created.

    SQL>
    SQL>insert into membership values (10,'A1','01-JAN-2000','31-DEC-2005');

    1 row created.

    SQL>insert into membership values (20,'A2','01-JAN-2001','31-DEC-2006');

    1 row created.

    SQL>insert into membership values (30,'A3','01-JAN-2002','31-DEC-2007');

    1 row created.

    SQL>
    SQL>Create table PERSON
    2 (person_number number(7)
    3 ,surname varchar2(10)
    4 ,initials varchar2(10)
    5 ,street_address varchar2(20)
    6 ,suburb varchar2(20)
    7 ,membership_number number(6));

    Table created.

    SQL>
    SQL>insert into person values (1,'Person-1','P1','Addr1','s1',10);

    1 row created.

    SQL>insert into person values (2,'Person-2','P2','Addr2','s2',20);

    1 row created.

    SQL>insert into person values (3,'Person-3','P3','Addr3','s3',30);

    1 row created.

    SQL>
    SQL>Create table PROVIDED_SERVICE
    2 (person_number number(7)
    3 ,doctor_number number(7)
    4 ,service_date date
    5 ,service_number number(3));

    Table created.

    SQL>
    SQL>--person 1 was provided services 1, 2 and 3 (I.e. 3 unique services) during August
    SQL>--person 2 was provided services 1,1,1 and 1 (1 unique service) during August
    SQL>--person 3 was provided services 1,2,3,3 (3 unique services) during August
    SQL>
    SQL>insert into PROVIDED_SERVICE values(1,11,'01-AUG-2003',1);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(1,11,'11-AUG-2003',2);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(1,11,'21-AUG-2003',3);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(2,12,'02-AUG-2003',1);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(2,12,'12-AUG-2003',1);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(2,12,'22-AUG-2003',1);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(3,13,'03-AUG-2003',1);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(3,13,'13-AUG-2003',2);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(3,13,'23-AUG-2003',3);

    1 row created.

    SQL>insert into PROVIDED_SERVICE values(3,13,'24-AUG-2003',3);

    1 row created.

    SQL>
    SQL>commit;

    Commit complete.

    SQL>select pe.surname
    2 , pe.initials
    3 , pe.membership_number
    4 , m.cover_type
    5 from membership m
    6 , person pe
    7 where pe.membership_number = m.membership_number
    8 and (
    9 select count(distinct service_number)
    10 from provided_service s
    11 where s.person_number = pe.person_number
    12 and service_date between '01-aug-2003' and '30-aug-2003')
    13 < (
    14 select AVG(cnt)
    15 from (
    16 select person_number, count(distinct service_number) cnt
    17 from provided_service
    18 where service_date between '01-aug-2003' and '30-aug-2003'
    19 group by person_number));

    SURNAME INITIALS MEMBERSHIP_NUMBER COV
    ---------- ---------- ----------------- ---
    Person-2 P2 20 A2

    1 row selected.

    SQL>

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Sep 2003
    Posts
    14
    thanks for your help
    Last edited by hallosenthil; 10-17-03 at 16:16.

Posting Permissions

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