Simple Query causing frustration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Query causing frustration

Author

Topic

Jackson
Starting Member

6 Posts

Posted - 2008-05-27 : 02:05:03

Hi, I am a new learner and user to SQL Sever 2005 and am having some major frustration trying to write a simple query.

I have two tables, 1) Ticket_Purchase, 2) Flight.

The Ticket_Purchase table has these columns: Ticket_Purchase_Number(PK), Flight_Number(FK), Date_Purchase_Made, Ticket_Price, Class_of_Ticket, Passenger_ID

The Flight table has these columns: Flight_Number(PK), Flight_Date, Flight_Departure_Time, Flight_Arrival_Time, Flight_Origin, Flight_Destination

I am trying to create a query that will tell me: On which flight were the most first class tickets sold?

There are only two types of classes; 'E' for economy and 'F' for First Class.

So far I am able to get a list of all the First class flights for each flight and can visually see which flight has the most first class tickets by counting them manually on the report generated, but I am totally confused on how to simply pull the single flight with the most First class tickets sold. I wonder if this requires something more like a join or a nested sub query?

The SQL I wrote for the above is:

Select Class_of_Ticket, Flight_Number
From Ticket_Purchase
Where Class_of_Ticket = ('F')
Order By Flight_Number;

And it produces:

Class_of_Ticket Flight_Number
--------------- -------------
F 1
F 1
F 1
F 2
F 2
F 3
F 3
F 3
F 3
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 4
F 5
F 5
F 6
F 6
F 6
F 7
F 7
F 8
F 8
F 8
F 9
F 9
F 9
F 9
F 9

(38 row(s) affected)

Rather I would like it to produce:
First_Class_Seats Purchased Flight_Number
--------------------------- ------------
14 4

I hope I didn't make this to confusing to understand as I am still learning the syntax and 'lingo' of how to communicate this stuff verbally.

Thank you for any help you could offer. It would be much appreciated.

Edit: the query report I pasted from SQL should have the flight number directly under the column header. For some reason the space between Class and Flight_number is being eliminated in the post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 02:09:17

[code]SELECT TOP 1 *
FROM
(
Select COUNT(*) AS TicketCount, Flight_Number
From Ticket_Purchase
Where Class_of_Ticket = ('F')
GROUP BY Flight_Number)t
Order By t.TicketCount DESC;[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 02:12:31

And for handle ties (more than one flight number has most number of first class tickets)

SELECT		TOP 1 WITH TIES
		Flight_Number,
		COUNT(*) AS TicketCount
FROM		Ticket_Purchase
WHERE		Class_of_Ticket = 'F'
GROUP BY	Flight_Number
ORDER BY	COUNT(*) DESC

E 12°55'05.25"
N 56°04'39.16"

Jackson
Starting Member

6 Posts

Posted - 2008-05-27 : 02:20:21

Wow, you are fast. Thank you so much!

One question for the first select:

SELECT TOP 1 *
FROM

Is it possible to write a HAVING or WHERE clause in replace of 'TOP 1 *' that would use a boolean and compare that to the nested Select and come up with the answer?

Again Thanks so much.

Jackson
Starting Member

6 Posts

Posted - 2008-05-27 : 02:23:54

Sorry, my 2nd post probably didn't make any sense. I just have not used TOP before. Thank you both for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 02:26:52

quote:
Originally posted by Jackson

Sorry, my 2nd post probably didn't make any sense. I just have not used TOP before. Thank you both for the help.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources