Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2012
    Posts
    24

    Unanswered: Simple Query Help

    Hello all,
    New here and yes this is classwork. No I'm not looking for just the answer lol. I need to understand this, not just pass the class. Anyways, here is the ERD Im using, and just below it is the output I need. I can do each on its own but not together. Any help appreciated Thanks
    http://img703.imageshack.us/img703/6460/erdversion1.jpg
    Create a customer report that lists all customer's names, the number of charters they have booked, and the total cost of all charters. Remember to include all customers, even customer’s that currently do not have any charters booked.

    What I have so far....
    Code:
    SELECT TRIM(customer.cus_fname) ||
          DECODE(customer.cus_initial,NULL, ' ',
           ' ' || customer.cus_initial || '. ')
          || TRIM(customer.cus_lname)  "Customer Name",
          COUNT(charter.char_trip) "# of Flights",
          (model.mod_chg_mile * charter.char_distance)* 1.35 "Charter Charge"
    FROM x.customer
    LEFT JOIN x.charter ON customer.cus_code = charter.cus_code
    JOIN x.aircraft ON charter.ac_number = aircraft.ac_number 
    JOIN x.model ON aircraft.mod_code = model.mod_code 
    GROUP BY customer.cus_fname, customer.cus_initial, customer.cus_lname, model.mod_chg_mile, charter.char_distance;
    Outputs...
    Code:
    Customer Name                                # of Flights Charter Charge
    -------------------------------------------- ------------ --------------
    Leona K. Dunne                                          1         989.82 
    Myron Orlando                                           1       1677.942 
    George Williams                                         1       5673.483 
    George Williams                                         1        2043.09 
    Leona K. Dunne                                          1      3687.4035 
    Leona K. Dunne                                          1       3373.812 
    Myron Orlando                                           1       4993.515 
    George Williams                                         1        1497.42 
    Alfred A. Ramas                                         1        2804.49 
    Myron Orlando                                           1       1323.594 
    James G. Brown                                          1      5929.4025 
    James G. Brown                                          1         1015.2 
    Leona K. Dunne                                          1       2049.435 
    Kathy W. Smith                                          1       4993.515 
    Kathy W. Smith                                          1        917.136 
    James G. Brown                                          1       3166.155 
    George Williams                                         1        2969.46 
    Olette K. Smith                                         1       1229.796 
    
     18 rows selected
    Before i added the last column, the # of flights were added and if one person didnt fly, they were listed too. Now they are not and need them to be. Any ideas?
    Last edited by Jptalon; 10-27-12 at 16:10. Reason: tags

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since we don't have your tables or data, we can not run, test or improve posted SQL.
    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
    Oct 2012
    Posts
    24
    Quote Originally Posted by anacedent View Post
    since we don't have your tables or data, we can not run, test or improve posted SQL.
    Does everyone here have access to everyone's tables and data?

    I posted my ERD of tables, my code, and output. Thought that's what I was suppose to do when looking for help. My bad

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    how exactly do I obtain functional SQL from a jpg file?
    The easier you make it for us to assist, it increases the probability for actual assistance.
    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
    Oct 2012
    Posts
    24
    Then I am missing what it is I am actually suppose to be posting when asking for help.....

    I will figure it our and post appropriately.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I believe that you need to use OUTER JOIN (at least, that's what the last sentence in your initial message suggests).

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Jptalon View Post
    Then I am missing what it is I am actually suppose to be posting when asking for help.
    It helps if you post the CREATE TABLE statements and sample data as INSERT INTO. Together with that, post the expected output.
    And of course and error message you get (not important for your case though though) or explain why the output you get is not what you want.

    That way it's very easy for us to create a test and verify any answer before posting. If you want to, you can alternatively post an example through SQL Fiddle

    As for your question. You have two problems in your current solution:

    1) you need to sum() the expression (model.mod_chg_mile * charter.char_distance) that way you can remove it from the group by expression and thus you don't get each customer multiple times

    2) As Littlefoot pointed out, currently you are only showing customers who have actually booked something. You will need to do an outer join (= left join) in order to get all customers and "nothing" from the charter table. so the count() and sum() will only take the customers into account that actually have a charter (aggregate columns will simply ignore NULL values)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by shammat View Post
    It helps if you post the CREATE TABLE statements and sample data as INSERT INTO. Together with that, post the expected output.
    Ahh this is what he meant. Now I am using SQL developer 11g. How would I get this for you?

    Quote Originally Posted by shammat View Post
    If you want to, you can alternatively post an example through SQL Fiddle
    Looking into it now.

    Quote Originally Posted by shammat View Post
    1) you need to sum() the expression (model.mod_chg_mile * charter.char_distance) that way you can remove it from the group by expression and thus you don't get each customer multiple times

    2) As Littlefoot pointed out, currently you are only showing customers who have actually booked something. You will need to do an outer join (= left join) in order to get all customers and "nothing" from the charter table. so the count() and sum() will only take the customers into account that actually have a charter (aggregate columns will simply ignore NULL values)
    #1 Done but still lists multiple customers
    #2 Been playing around with this since he posted. Everything i have done has only returned more then my original rows. Wrong direction lol

    Thanks, actually some help. Wish my Professor did

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Jptalon View Post
    Ahh this is what he meant. Now I am using SQL developer 11g. How would I get this for you?
    No idea. I'm not using SQL Developer, but there should be an option to show the table's create statement and export some rows as INSERT statements.


    #1 Done but still lists multiple customers
    #2 Been playing around with this since he posted. Everything i have done has only returned more then my original rows. Wrong direction lol
    Try this:
    Code:
    SELECT TRIM(customer.cus_fname) ||
          DECODE(customer.cus_initial,NULL, ' ',
           ' ' || customer.cus_initial || '. ')
          || TRIM(customer.cus_lname)  "Customer Name",
          COUNT(charter.char_trip) "# of Flights",
          sum(model.mod_chg_mile * charter.char_distance)* 1.35) "Charter Charge"
    FROM x.customer
      LEFT JOIN x.charter ON customer.cus_code = charter.cus_code
      LEFT JOIN x.aircraft ON charter.ac_number = aircraft.ac_number 
      LEFT JOIN x.model ON aircraft.mod_code = model.mod_code 
    GROUP BY customer.cus_fname, customer.cus_initial, customer.cus_lname
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A short tutorial for you (in a case you didn't get the idea), based on Scott's schema.

    There are 4 departments in the DEPT table:
    Code:
    SQL> select deptno, dname
      2  from dept
      3  order by deptno;
    
        DEPTNO DNAME
    ---------- --------------
            10 ACCOUNTING
            20 RESEARCH
            30 SALES
            40 OPERATIONS
    
    SQL>
    This is a list of employees; note that noone works in department 40:
    Code:
    SQL> select deptno, ename
      2  from emp
      3  order by deptno, ename;
    
        DEPTNO ENAME
    ---------- ----------
            10 CLARK
            10 KING
            10 MILLER
            20 ADAMS
            20 FORD
            20 JONES
            20 SCOTT
            20 SMITH
            30 ALLEN
            30 BLAKE
            30 JAMES
            30 MARTIN
            30 TURNER
            30 WARD
    
    14 rows selected.
    OK, let's join these two tables in order to display department name:
    Code:
    SQL> select d.deptno, d.dname, e.ename
      2  from emp e, dept d
      3  where e.deptno = d.deptno
      4  order by d.deptno, e.ename;
    
        DEPTNO DNAME          ENAME
    ---------- -------------- ----------
            10 ACCOUNTING     CLARK
            10 ACCOUNTING     KING
            10 ACCOUNTING     MILLER
            20 RESEARCH       ADAMS
            20 RESEARCH       FORD
            20 RESEARCH       JONES
            20 RESEARCH       SCOTT
            20 RESEARCH       SMITH
            30 SALES          ALLEN
            30 SALES          BLAKE
            30 SALES          JAMES
            30 SALES          MARTIN
            30 SALES          TURNER
            30 SALES          WARD
    
    14 rows selected.
    Fine; but, just like you, I want to display department 40 as well, although there's noone working in it. Therefore, I'll use outer join:
    Code:
    SQL> select d.deptno, d.dname, e.ename
      2  from emp e, dept d
      3  where e.deptno (+) = d.deptno
      4  order by d.deptno, e.ename;
    
        DEPTNO DNAME          ENAME
    ---------- -------------- ----------
            10 ACCOUNTING     CLARK
            10 ACCOUNTING     KING
            10 ACCOUNTING     MILLER
            20 RESEARCH       ADAMS
            20 RESEARCH       FORD
            20 RESEARCH       JONES
            20 RESEARCH       SCOTT
            20 RESEARCH       SMITH
            30 SALES          ALLEN
            30 SALES          BLAKE
            30 SALES          JAMES
            30 SALES          MARTIN
            30 SALES          TURNER
            30 SALES          WARD
            40 OPERATIONS
    
    15 rows selected.
    Cool, here it is - department 40!

  11. #11
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by shammat View Post
    No idea. I'm not using SQL Developer, but there should be an option to show the table's create statement and export some rows as INSERT statements.
    Found them, they blocked copy, but the site wont accept them anyways. Says missing command or something. Prolly for this reason

  12. #12
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by shammat View Post
    Try this:
    Code:
    SELECT TRIM(customer.cus_fname) ||
          DECODE(customer.cus_initial,NULL, ' ',
           ' ' || customer.cus_initial || '. ')
          || TRIM(customer.cus_lname)  "Customer Name",
          COUNT(charter.char_trip) "# of Flights",
          sum(model.mod_chg_mile * charter.char_distance)* 1.35) "Charter Charge"
    FROM x.customer
      LEFT JOIN x.charter ON customer.cus_code = charter.cus_code
      LEFT JOIN x.aircraft ON charter.ac_number = aircraft.ac_number 
      LEFT JOIN x.model ON aircraft.mod_code = model.mod_code 
    GROUP BY customer.cus_fname, customer.cus_initial, customer.cus_lname
    GROUP BY stood out to me. I have this exactly except i added all other columns into the GROUP BY as my book states it has to if it is a nonaggregate. Grrrr
    Took them out and it works exactly. Now to format the last column.
    Last edited by Jptalon; 10-27-12 at 18:21. Reason: typo

  13. #13
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by Jptalon View Post
    GROUP BY stood out to me. I have this exactly except i added all other columns into the GROUP BY as my book states it has to if it is a nonaggregate. Grrrr
    Took them out and it works exactly. Now to format the last column.
    Yes I realize what I just said. But I wont edit it lol. I added them before playing with SUM, then forgot that they actually needed to be taken out. Thank you all

Posting Permissions

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