Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    5

    Unanswered: really simple query problem?!

    Bascically, i have to create a query to preform this:

    "For the aircraft 4278Y list the model of aircraft, how many seats it has, and how many miles it has fown between the 5th and 10th of feb 2006 (inclusive)."

    its easy enough to get it to return all the details, but for some reason the SUM wont ever work? any ideas?

    heres the code i was working with previously:

    Code:
    SELECT SUM(Charter.distance) as total, charter.distance, aircraftdetails.seats, aircraftdetails.name, charter.date
    
    FROM Charter, Aircraftdetails, Aircraftid
    
    Where Aircraftdetails.[Aircraft code] = Aircraftid.[Aircraft code] AND Aircraftid.[Aircraft Number] = Charter.Accountnumber
    
    AND Aircraftid.[Aircraft number] = "4278Y"
    AND charter.date >= '05/02/2006' AND charter.date <= '10/02/2006'
    
    GROUP BY charter.distance, aircraftdetails.seats, aircraftdetails.name, charter.date
    ORDER BY charter.distance, aircraftdetails.seats, aircraftdetails.name, charter.date;
    im guessing its something simple but i've been pulling my hair out for 2 days over this!

    Cheers,

    Jamie

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the sum won't work properly because you are asking for a sum for each individual distance

    let's say you took trips with distances of 12, 15, 9, 11, 15, 7, and 12 miles

    the sums you will get are --

    for distance 7, sum = 7
    for distance 9, sum = 9
    for distance 11, sum = 11
    for distance 12, sum = 24
    for distance 15, sum = 15

    do you understand what i've just explained and why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    5
    ok, i understand that!

    so i'd be able to call the sum if i just selected that field like

    Select Sum(charter.distance)
    From etc.etc.etc.

    how would it be possible then to incorporate this into the query? as i must return how many seats and the aircraft model aswell?

    would the answer be to create another query, which will in effect hold the value of the sum? then have a simple query to just get the other data, and just mold them into one query?

    sorry for seeming so dumb, its just this is our coursework for christmas, and it has to be SQL ... and we start learning sql in janurary. Only at university would that ever happen!

    Cheers for any help!

    Jamie

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the secret is the GROUP BY clause

    the GROUP BY clause will produce one row for every combination of values for its column(s)

    in my last post, i said you'd get one sum for each individual distance, because you ahve distance in the GROUP BY

    pull it out of there (and also from the SELECT) and try your query again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Posts
    5
    Oh My God!

    you're a life saver!

    took the date and the other stuff out of the GROUP & ORDER BY clauses and it works absolutly spot on!

    Cheers Mate!

    Another quick question though ...

    some employees have a medical ceritificate in the certificates table, along with a few others (i.e. type of pilot license they have etc.)

    is there any way to display all of these except this one certificate? im guessing a logical NOT? (i.e. WHERE certificate <> "med" )

    along with this information is the date it was issued, how would i go about creating a query which would return all certificates from pilots who got a medical certificate in 2004?

    i dont want any code, just a rough idea of how to go about doing it, if you could?

    you've been too helpful so far already so i'll understand already if you cant be bothered!

    Cheers

    Jamie

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's not that i cannot be bothered, it's that i cannot understand your last question

    would you care to try stating it again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Posts
    5
    well, basically, i have a certifications table to hold that certifications pilots have

    ie. mid-engine, fixed wing etc.

    also in there is a medical certificate (as we need to know there fit to fly).

    i wanted to know if there was there any way to return all but the single meical certificate, if the medical certificate was issued in 2004.

    i have the basis here ...

    Code:
    SELECT employees.FName, employees.LName, certification.Code, transactions.type
    FROM employees, certification, transactions
    WHERE employees.[employee id]=certification.[flightcrew id] And employees.[employee id] = transactions.[employee id]
    AND transactions.type = "pilot"
    AND(certification.[date of issue] >= #01/01/2004#)
    AND(certification.[date of issue] <= #31/12/2004#)
    AND(certification.code <> "med");
    but this returns all certificates issued in 2004 except medical certificates.

    basically, what im trying to do is list the name of the pilots who underwent a medical exam in 2004 and list the licenses they hold.

    any ideas?

    cheers!

    Jamie

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    revist your bracketing
    i''ve always found that SQL where clauses can get a bit complex unless you think clearly what it is you want.

    the SQL engine parses each where clause accorind to the brackets, including a value from the table according to whether it passes the logival test being applied

    So Id suggest revisting what it is you are trying to request from the db
    so you want all certificates....
    unless they are medical certicificates issued outside of 2004

    if you want only one medical certificate issued in 2004 then you have another problem.

    incidentally I'd suggest using med as you certificate type ins't the best solution.. you may want to consider abstracting that out so that certificate type is a foreign key into another table, after all to a computer med isn't necessarily the same as MED or Med

  9. #9
    Join Date
    Dec 2007
    Posts
    5
    yeah, thats what i did in the end!

    i just stuck a "medical examination" date in the employees table, and just used that information. slightly less efficient, but saves alot of hassle!

    then just queried for employees with a medical exam in 2004 and all the certificates they had =].

    but hey, it works!

    thanks for clearing up the grouping aswell by the way, it'll give me something to play with later incase i try it the otherway!

    Thanks,

    Jamie

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    TOTAL GARBAGE - withdrawn

    izy
    currently using SS 2008R2

Posting Permissions

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