Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    32

    Question Unanswered: quick query question...displays counts =0

    Hey guys.. I am just after a little help in creating a simple query.

    I have a database with a table called GENRES, and a composite table called TABLE-GENRES. I have to create a query which lists ALL genres, and shows how many titles each genre has. So far I have created the following query...

    Code:
    /* For all Genres, lists the number of titles in each classification which are in the Movies table */
    SELECT GENRE.GENRE_ID AS "Genre ID",
           GENRE.GENRE_DESCRIPTION AS "Genre Classification",
           COUNT(*) AS "Number of Titles"
    FROM   GENRE,MOVIE_GENRE
    WHERE  GENRE.GENRE_ID=MOVIE_GENRE.GENRE_ID
    GROUP BY GENRE.GENRE_ID,GENRE.GENRE_DESCRIPTION;
    But my problem is that my Query has to deal with the issue of when a GENRE has 0 Titles in it, that is, I have to still display the Genre name and display 0.

    How would I do this? I am extremely stuck on this!

    Any help would be mostly appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT GENRE.GENRE_ID AS "Genre ID",
           GENRE.GENRE_DESCRIPTION AS "Genre Classification",
           COUNT(*) AS "Number of Titles"
    FROM   GENRE,MOVIE_GENRE
    WHERE  GENRE.GENRE_ID=MOVIE_GENRE.GENRE_ID(+)
    GROUP BY GENRE.GENRE_ID,GENRE.GENRE_DESCRIPTION;
    might give you what you desire.
    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.

  3. #3
    Join Date
    Aug 2007
    Posts
    32
    Thanks for that mate... we are almost there! The only problem now is that things with 0 are displaying as 1... as shown below...

    Code:
    Genre ID Genre Classification Number of Titles       
    -------- -------------------- ---------------------- 
    MY       Mystery              2                      
    HO       Horror               1                      
    DR       Drama                4                      
    AC       Action               4                      
    CR       Crime                2                      
    AD       Adventure            1                      
    TR       Thriller             4                      
    MU       Music                1                      
    CO       Comedy               4                      
    SF       Sci-Fi               2                      
    RO       Romance              4

    ^^^^Above is the query i had that wouldnt display 0's

    Code:
    Genre ID Genre Classification Number of Titles       
    -------- -------------------- ---------------------- 
    BI       Biography            1                      
    HO       Horror               1                      
    MY       Mystery              2                      
    SH       Short                1                      
    DR       Drama                4                      
    NE       News                 1                      
    GS       Game-Show            1                      
    ML       Musical              1                      
    WN       Western              1                      
    WR       War                  1                      
    AC       Action               4                      
    AD       Adventure            1                      
    CR       Crime                2                      
    DO       Documentary          1                      
    HI       History              1                      
    SP       Sport                1                      
    TR       Thriller             4                      
    AN       Animation            1                      
    FN       Film-Noir            1                      
    MU       Music                1                      
    CO       Comedy               4                      
    FA       Family               1                      
    SF       Sci-Fi               2                      
    FY       Fantasy              1                      
    RO       Romance              4                      
    RT       Reality-TV           1                      
    TS       Talk-Show            1
    ^^^^ Above is the code you just gave me which is almost working but giving 1's where there is no count.. anymore ideas? cheers for that

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'm not sure if this will work or be any better.
    Code:
    SELECT GENRE.GENRE_ID AS "Genre ID",
           GENRE.GENRE_DESCRIPTION AS "Genre Classification",
           COUNT(MOVIE_GENRE.GENRE_ID) AS "Number of Titles"
    FROM   GENRE,MOVIE_GENRE
    WHERE  GENRE.GENRE_ID=MOVIE_GENRE.GENRE_ID(+)
    GROUP BY GENRE.GENRE_ID,GENRE.GENRE_DESCRIPTION;
    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
    Aug 2007
    Posts
    32
    Mate thats exactly what I tryed about 5 minutes ago before reading your post and it works a treat. Thanks for ya help!

  6. #6
    Join Date
    Aug 2007
    Posts
    32
    Just one last one mate... do you see any problems with the following trigger? I am trying to have it display a message every 20 rows in RENTAL (eg, after every 20 rentals, display the message).
    The trigger compiles fine, but to test it out, I changed the IF statement a bit (you can see in the comment my original statement) so that I could see if the trigger will actually display a message with every new insert of a rental (as the amount of rows is above 0), but nothing is happening, I am just getting "1 row inserted" when I insert a new row...... can you see anything logically wrong here?

    Code:
    CREATE OR REPLACE TRIGGER clean_disc
    BEFORE INSERT ON rental
    DECLARE
    	loc_counter	NUMBER;
    BEGIN
    	SELECT COUNT(*) INTO loc_counter
    	FROM rental;
    
    	/*IF mod(loc_counter,20) = 0 THEN*/
                 IF loc_counter >0 THEN
    	DBMS_OUTPUT.PUT_LINE('This disc requires cleaning! Please clean before rented to customer');
            END IF;
    END;
    /
    Thanks

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    You shall have enabled output, eg.by SET SERVEROUTPUT ON in SQL*Plus to make DBMS_OUTPUT work. Bear in mind, some clients (you did not mentioned your one) may NOT be able display it at all.

    By the way, your SELECT statement does not have any condition, so it fires after 20 rentals of ANY discs. Or do you rent just one disc in total?
    Also you are likely to have performance problems in the future.
    I would change data model - add column with number of rentals directly into disc table and simply increase/reset it on rental/cleaning actions.

  8. #8
    Join Date
    Aug 2007
    Posts
    32
    Ok thanks for that.. i am using Oracle.. i just turned on the SERVEROUTPUT and it still is only displaying "1 row inserted" when i insert into the RENTAL table... any ideas why?

    And i dont have any conditions because it is meant to fire after 20 rentals of any disc (which i think is a bit stupid, but thats how the assignment states)

    Thanks mate

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    No ideas without seeing your session.
    Code:
    SQL> create table t1 ( c1 integer );
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER tg1 BEFORE INSERT ON t1
      2  DECLARE
      3    loc_counter number;
      4  BEGIN
      5    SELECT COUNT(*) INTO loc_counter from t1;
      6    IF loc_counter > 0 THEN
      7      DBMS_OUTPUT.PUT_LINE('Row count: '||to_char(loc_counter));
      8    END IF;
      9  END;
     10  /
    
    Trigger created.
    
    SQL> set serveroutput on
    SQL> insert into t1 values ( 1 );
    
    1 row created.
    
    SQL> insert into t1 values ( 2 );
    Row count: 1
    
    1 row created.
    
    SQL>
    For me it displays the message.
    Just after the second insert, as in BEFORE INSERT you do not see the inserted row.

  10. #10
    Join Date
    Aug 2007
    Posts
    32
    Ok I am using Oracle and tryed using the code you wrote... when I try to turn serveroutput on.. i get the following:

    Code:
    create table succeeded.
    TRIGGER tg1 Compiled.
    line 1: SQLPLUS Command Skipped: set serveroutput on
    Any ideas why? Is their a different command for oracle perhaps?

    Cheers mate

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use CUT & PASTE to show us the WHOLE session from invoking SQL*Plus through SQL> EXIT

    Code:
    sqlplus
    username/password
    SQL> set term on echo on
    .
    .
    .
    .
    SQL> EXIT
    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.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by lockmac
    Ok I am using Oracle and tryed using the code you wrote.
    Of course you are using Oracle because that is your (backend) DBMS, but which client (= frontend) are you using?
    Seems to me you are not using SQL*Plus.

  13. #13
    Join Date
    Aug 2007
    Posts
    32
    Sorry guys i am quite new to this... yes I am using Oracle and as my front end i am using Oracle SQL developer...

  14. #14
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    set serveroutput is a SQL*Plus command.
    SQLDeveloper does not understand that I think

    I'm not too experienced with SQL Developer, but I think you need to switch to the "DBMS Output" tab in the worksheet, then click on the "Enable DBMS Output" button in the little toolbar (leftmost button).

    But I think this is only supported in newer versions of SQL Developer.

    You might want to check the SQL Developer forum at Oracle's site:
    http://forums.oracle.com/forums/forum.jspa?forumID=260

Posting Permissions

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