Group By - Help Please

Hello

If you can help or offer suggestion i would appreciate it.

This is what i have:

SELECT Planes.[Plane_ID], Planes.[Num_Seats], Depart_Date, Plane_Type
FROM Planes, Flights
WHERE [Depart_Date] Like "8*";

Which looks like this:
Plane_ID Num_Seats Depart_Date Plane_Type
1 450 8/9/2007 Boeing 717
1 450 8/1/2008 Boeing 717
1 450 8/1/2008 Boeing 717
2 450 8/9/2007 Boeing 717
2 450 8/1/2008 Boeing 717
2 450 8/1/2008 Boeing 717
3 589 8/9/2007 Boeing 747
3 589 8/1/2008 Boeing 747
3 589 8/1/2008 Boeing 747
4 589 8/9/2007 Boeing 747
4 589 8/1/2008 Boeing 747
4 589 8/1/2008 Boeing 747
5 589 8/9/2007 Boeing 747
5 589 8/1/2008 Boeing 747
5 589 8/1/2008 Boeing 747
6 12 8/9/2007 Cesna A12
6 12 8/1/2008 Cesna A12
6 12 8/1/2008 Cesna A12
7 24 8/9/2007 CesnaA24
7 24 8/1/2008 CesnaA24
7 24 8/1/2008 CesnaA24
8 455 8/9/2007 Boeing 720
8 455 8/1/2008 Boeing 720
8 455 8/1/2008 Boeing 720
9 56 8/9/2007 CesnaA56
9 56 8/1/2008 CesnaA56
9 56 8/1/2008 CesnaA56

BUT... I want to group it by plane tyoe so i don't have the duplicates. Once i have done that then what i really want to do is sum the available seats per plane type
So that i will have something which should look like:
PLANE TYPE NUMBER of SEATS
Boieng747 1767
etc.......

I have tried to group by, use distinct but i can't figure it out....can any one help? i am a distance student and i need help for my assigment

OK If I understand you correctly, If your table had only these three rows:

Plane_ID Num_Seats Depart_Date Plane_Type
1 450 8/9/2007 Boeing 717
1 450 8/1/2008 Boeing 717
1 450 8/1/2008 Boeing 717

You would get

PLANE TYPE NUMBER of SEATS
Boeing717 900

There are 2 ways to do this. I'm not a MySQL person so the first may not be viable.

1. is to create a stored query or view that does the Distinct to get rid of the duplicates and then create a second query for the GroupBy. For the second query the From clause would reference the stored query:

SELECT ..
FROM <query1>
GroupBy ....

2. Do this all in a single query which looks like this:

SELECT P.Plane_Type, Sum(P.Num_Seats) as totalSeats
FROM (SELECT Distinct Plane_ID, Num_Seats, Depart_Date, Plane_Type FROM Planes WHERE Depart_Date like "8%") as P
GROUPBY P.Plane_Type


The advantage of the first method is that it is easy to
troubleshoot and modify.

Thanks for your help......its greatly appreciated.

i tired your second option cause i didn't understand how to do the first one

you said:
SELECT P.Plane_Type, Sum(P.Num_Seats) as totalSeats
FROM (SELECT Distinct Plane_ID, Num_Seats, Depart_Date, Plane_Type FROM Planes WHERE Depart_Date like "8%") as P
GROUPBY P.Plane_Type

I entered it as
SELECT Planes.Plane_Type, Sum(Planes.Num_Seats) as totalSeats
FROM (SELECT Distinct Plane_ID, Num_Seats, FROM Planes) (SELECT Distinct Depart_Date, FROM Flights WHERE Depart_Date like "8*") as P
GROUPBY P.Plane_Type

But i have stuffed it up in the FROM CLAUSE
you see the Depart_Date comes from the table Flights so i tried to add that in there but know luck.

I've been trying to find another way to do this query:
What i have also been trying for the last 8 hours is to make two seperate quires one thatshows all flights in august and one that shows the total of seats on all plane types - which i have done. But i do not know how to add these two quirese together
First one
SELECT Planes.Plane_Type, Sum(Planes.Num_Seats) AS SumOfNum_Seats INTO [AVAILABLE SEATS PER PLANE TABLE]
FROM Planes, Flights
GROUP BY Planes.Plane_Type
HAVING (((Sum(Planes.[Num_Seats]))<>False));
Second One
SELECT Planes.Plane_Type, Sum(Planes.Num_Seats) as totalSeats
FROM (SELECT Distinct Plane_ID, Num_Seats, FROM Planes), (SELECT Distinct Depart_Date, FROM Flights WHERE Depart_Date like "8%") as P
GROUP BY Planes.Plane_Type
any suggestions would be great cause i really do not want to fail my assignment, thanks for helping me

alright let's back up.

What is the structure of the planes table and the flights table?

I hope this comes across clear - is this whatyou mean structurei copy and paste


Plane_ID Plane_Type Num_Seats Range_KM
1 Boeing 717 450 15890
2 Boeing 717 450 20100
3 Boeing 747 589 74000
4 Boeing 747 589 34201
5 Boeing 747 589 65442
6 Cesna A12 12 17546
7 CesnaA24 24 124124
8 Boeing 720 455 21555
9 CesnaA56 56 100012


Flight_ID Plane_ID Depart_Place Depart_Date Depart_Time Arrive_Place Arrive_Date Arrive_Time
1 1 Rockhampton 3/1/2008 3:00:00 PM Melbourne 3/1/2008 7:00:00 PM
2 1 melbourne 4/5/2008 12:00:00 PM Sydney 4/5/2008 2:00:00 PM
3 3 Cairns 11/1/2008 7:00:00 AM Brisbane 11/1/2008 8:00:00 AM
4 3 Brisbane 8/9/2007 11:00:00 AM Cairns 8/9/2007 12:00:00 PM
5 2 Sydney 8/1/2008 5:00:00 PM Melbourne 8/1/2008 7:35:00 PM
6 4 Perth 8/1/2008 9:15:00 AM Adelaide 8/1/2008 2:20:00 PM
7 5 Adelaide 9/1/2008 3:45:00 PM Perth 9/1/2008 9:30:00 PM
8 6 Melbourne 10/1/2008 11:20:00 AM Hobart 10/1/2008 12:45:00 PM
9 1 Rockhampton 11/4/2008 9:15:00 AM Sydney 11/4/2008 12:25:00 PM

OK well the reason your query returned duplicates is becuase you simply selected from the 2 tables rather than doing a join between the 2 table.

You should lookup what the join does but here are the queries you want:

SELECT plane_ID, num_seats, Depart_date, plane_type
FROM planes INNER JOIN Flights ON planes.plane_ID = Flights.plane_ID
WHERE Depart_date like "8%"


If you just want the total number of seats for a particular plane type then the query is:

SELECT plane_type, SUM(num_seats) as totalSeats
FROM planes
GROUP By Plane_Type

If you want the total number of seats of flights in august by plane type then the query would be:

SELECT plane_type, SUM(num_seats) as totalSeats
FROM (SELECT plane_ID, num_seats, Depart_date, plane_type
FROM planes INNER JOIN Flights ON planes.plane_ID = Flights.plane_ID
WHERE Depart_date like "8%")
GROUP By plane_type

that should do it.