Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: simple question for pro users

    Hello guys,
    I decided to post the question to u after two days investigation of one of the query which I still can’t make it;/ mainly what I need to do is to find showings of movies that have occupancy below the average occupancy for showing of that movie.
    Simply there are two tables involved into the query such as showing and booking
    Create table Showing(showing_id NUMBER(5) PRIMARY KEY,
    show_date DATE,
    movie_id NUMBER(4));
    insert into showing values(00001, '12-MAR-2009', 1111);
    insert into showing values(00002, '12-APR-2009', 2222);
    insert into showing values(00003, '12-MAY-2009', 1111);
    insert into showing values(00004, '12-JUN-2009', 2222);


    Create table Booking(client_id NUMBER(6),
    showing_id NUMBER(5) CONSTRAINT fk_sh REFERENCES showing,
    CONSTRAINT pk_as PRIMARY KEY(client_id, showing_id));
    insert into booking values(700001, 00001);
    insert into booking values(700002, 00001);
    insert into booking values(700003, 00002);
    insert into booking values(700004, 00003);
    insert into booking values(700005, 00004);

    what I was trying to achieve is to get:
    movie_id, showing_id, avg(occupancy for showing of that movie),count(showing occupancy)

    if anyone can help me with it I would be really appreciate.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the title of your thread is inaccurate, it should say "simple question for people willing to do my homework for me"

    we'll help you, but you have to do the work

    start by writing a small query to show the average occupancy for each movie
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    8
    sorry but u r wrong. i really spent 2 days of trying doing it in different way but i couldnt managed to do it that is why i was asking here. i didnt know that u expect me to prove that i did some work. however...there is some code which i have already done:

    select s.showing_id, count(client_id) from showing s, booking b
    where s.showing_id = b.showing_id (+)
    group by s.showing_id
    having count(client_id) < (select avg(cnt) from
    (select sh.showing_id, count(client_id) cnt
    from showing sh, booking b where sh.showing_id = b.showing_id
    group by sh.showing_id));

    or

    select s.showing_id, count(client_id)
    from movie m, showing v, booking b where m.movie_id_id = s.showing_id and s.showing_id = b.showing_id
    group by s.showing_id
    having count(client_id)>
    (select count(cnt) from
    ( select m.movie_id, count(client_id) / count(distinct(b.showng_id)) cnt
    from showing s, movie m, booking b where m.movie_id = s.movie_id and s.showing_id = b.showing_id
    group by m.movie_id
    ));

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what I need to do is to find showings of movies that have occupancy below the average occupancy for showing of that movie.

    Which field in which table holds "occupancy" data?

    What useful information is contained in BOOKING table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2010
    Posts
    4
    maybe because occupancy is not state in column and need to be calculated?
    Last edited by pastus; 04-05-10 at 15:01.

  6. #6
    Join Date
    Apr 2010
    Posts
    8
    we can find occupancy by using count(client_id) from booking table grouping by showing_id . count will give us number of people who were watching particural movie on particural show.

    select s.showing_id, count(client_id) from showing s, booking b where s.showing_id = b.showing_id(+) group by s.showing_id

    so, client_id is the only one useful information from booking table
    Last edited by unseen; 04-05-10 at 15:41.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by unseen View Post
    count(client_id) from showing s, booking b where s.showing_id = b.voyage_id(+) group by s.showing_id
    OH MY!

    from where did "voyage_id" magically appear?

    Does PK/FK relationship exist between SHOWING & BOOKING tables?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anacedent View Post
    from where did "voyage_id" magically appear?
    from the disguised homework assignment

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2010
    Posts
    8
    i dont need a solution. i would like to only know what is wrong in my code. where i need to make changes to run the query

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How would an independent observer conclude this problem has been successfully solved?

    Why do you not answer my questions?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Apr 2010
    Posts
    8
    Quote Originally Posted by anacedent View Post
    How would an independent observer conclude this problem has been successfully solved?

    Why do you not answer my questions?

    of course there are fk/pk such as:

    movie_id as a fk in showing table
    showing_id as a fk in booking table

    Create table Movie(movie_id NUMBER(4) PRIMARY KEY,
    title varchar2(30));

    Create table Showing(showing_id NUMBER(5) PRIMARY KEY,
    show_date DATE,
    movie_id NUMBER(4) CONSTRAINT fk_movie REFERENCES movie);


    Create table Booking(client_id NUMBER(6),
    showing_id NUMBER(5) CONSTRAINT fk_sh REFERENCES showing,
    CONSTRAINT pk_as PRIMARY KEY(client_id, showing_id));

  12. #12
    Join Date
    Apr 2010
    Posts
    8
    i dont understand why when i write these syntax:

    select m.movie_id,
    title,
    count(showing_id)
    from movie m,
    showing s
    where m.movie_id = s_movie_id
    group by m.movie_id, title

    it will give me right number of showing for each particural movie

    and the syntax below will give me proper result of number of clients:

    select m.movie_id,
    title,
    count(client_id)
    from movie m,
    showing s ,
    booking b
    where m.movie_id = s_movie_id and s.showing_id = b.showing_id
    group by m.movie_id, title

    but when i put them together to calculate avg occupancy for each movie the result of count(showing_id) has not given me good number. this happened after adding booking table to the query;

    select m.movie_id,
    title,
    count(showing_id) ,
    count(client_id), count(showing_id) / count(client_id) as avg
    from movie m,
    showing s ,
    booking b
    where m.movie_id = s_movie_id and s.showing_id = b.showing_id
    group by m.movie_id, title

  13. #13
    Join Date
    Apr 2010
    Posts
    8
    i was also tried to do it in this way :

    Select m.movie_id,
    m.title,
    count(showing_id),
    sub.count
    From showing s, movie m,(select mo.movie_id,
    title,
    count(client_id) as count
    from movie mo, showing sh, booking b
    where mo.movie_id = sh.showing_id and sh.showing_id = b.showing_id
    group by mo.movie_id, title
    ) sub
    where m.movie_id = sub.movie_id
    group by m.movie_id, m.title;


    but ive got this;
    ERROR at line 4:
    ORA-00979: not a GROUP BY expression

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT m.movie_id,
           m.title,
           COUNT(showing_id),
           sub.COUNT
    FROM   showing s,
           movie m,
           (SELECT mo.movie_id,
                   title,
                   COUNT(client_id) AS COUNT
            FROM   movie mo,
                   showing sh,
                   booking b
            WHERE  mo.movie_id = sh.showing_id
                   AND sh.showing_id = b.showing_id
            GROUP  BY mo.movie_id,
                      title) sub
    WHERE  m.movie_id = sub.movie_id
    GROUP  BY m.movie_id,
              m.title;
    How will an independent observer conclude a correct result set has been produced?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Aug 2009
    Posts
    262
    How will an independent observer conclude a correct result set has been produced?


    being an independent observer , i would surely like this thread .......... making me giggle ...

    can i have the names of the movies ? or this show ?

Posting Permissions

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