# Thread: really simple query problem?!

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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?

3. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

5. Registered User
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?

Cheers

Jamie

6. SQL Consultant
Join Date
Apr 2002
Location
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?

7. Registered User
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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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. Registered User
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. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
TOTAL GARBAGE - withdrawn

izy

#### Posting Permissions

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