SQL Query Question

[ShMiL]

SELECT sum06clients.name,SUM(bikoorim.nights) as totalnights FROM bikoorim INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id GROUP BY bikoorim.clientID ORDER BY totalnights

Suppose to do this:
"Select the [name] field from the customers table, and the sum of all the nights of this customer (by clientID) - call the sum of the nights: 'totalnights'.
Since the customers table and the visits (bikoorim) tables are seperated, join them by the connection: 'bikoorim.clientID = sum06clients.id'.
Organize all this sh*t by totalnights"

Too bad IIS can't understand MY English...


Can someone please help?

PS
the error is:

You tried to execute a query that does not include the specified expression 'name' as part of an aggregate function

[Roelf]

you have to group on name also:
SELECT sum06clients.name, SUM(bikoorim.nights) as totalnights
FROM bikoorim
INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id
GROUP BY bikoorim.clientID, sum06clients.name
ORDER BY totalnights

[ShMiL]

Thanks!

"SELECT sum06clients.name, SUM(bikoorim.nights) as totalnights FROM bikoorim INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id GROUP BY bikoorim.clientID, sum06clients.name"

Is working fine
the problem is when I add:"ORDER BY totalnights" at the end,
I get:
"Too few parameters. Expected 1."

It seems like totalnights doesn't "exist"...
What can I do?

[ShMiL]

"SELECT sum06clients.name, SUM(bikoorim.nights) as totalnights FROM bikoorim INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id GROUP BY bikoorim.clientID, sum06clients.name ORDER BY SUM(bikoorim.nights) desc"

Works fine...

What do you think? Is it ok?

[hulan]

You tried to execute a query that does not include the specified expression 'depart'

Hi I have the same error message, when I try to find the flights that have the same destination and depart countries, and income from those flights.

My SQL syntax is:

SELECT [FlightNo], [Depart], [Destination], SUM(NumSeats*SeatPrice) AS INCOME
FROM Flight INNER JOIN Booking on Flight.FlightNo = Booking.Flight
WHERE [Flight].[Depart]=[Flight].[Destination]
GROUP BY [Flight].[FlightNo]
ORDER BY [Flight].[FlightNo], [Flight].[Depart];
And my table structures are:

Table Booking has Table Flight has
BookingNo FlightNo
Customer Depart
Flight Destination
NumSeats Plane
Seat Price DepDate
StudentNo FCostFixed
FCostPassenger

If anyone have time and knowledge, your help'd be much appreciated.
regards,
hulan


PS
the error is:

You tried to execute a query that does not include the specified expression 'depart' as part of an aggregate function

[/QUOTE]

[hulan]

You tried to execute a query that does not include the specified expression 'depart'

Oops, sorry if i confused you, the table structures are:

Table Booking has
BookingNo
Customer
Flight
NumSeats
Seat Price StudentNo

Table Flight Has

FlightNo
Depart
Destination
Plane
DepDate
FCostFixed
FCostPassenger



regards,
hulan

PS
the error is:

You tried to execute a query that does not include the specified expression 'depart' as part of an aggregate function

[/QUOTE]
Flight
NumSeats
Seat Price
StudentNo


If anyone have time and knowledge, your help'd be much appreciated.
regards,
hulan


PS
the error is:

You tried to execute a query that does not include the specified expression 'depart' as part of an aggregate function

[/QUOTE]

[Roy Sinclair]

The basic problem is exactly the same as the previous one but since you didn't understand what was wrong (and nobody explained it) you're stuck wondering too.

The key to getting rid of this error is to know that all the data fields except the one in the "Sum" that you select in your select statement must also be included in the "Group by" list.

Specifically, you need to add the "Depart" and "Destination" fields to your "Group by" list.

[hulan]

The search key was not found in any record

The basic problem is exactly the same as the previous one but since you didn't understand what was wrong (and nobody explained it) you're stuck wondering too.

The key to getting rid of this error is to know that all the data fields except the one in the "Sum" that you select in your select statement must also be included in the "Group by" list.

Specifically, you need to add the "Depart" and "Destination" fields to your "Group by" list.

Thank you Roy Sinclair, I tried it with Group By except those fields included in the Sum, however now I get error message like

"The search key was not found in any record"

and the query was

SELECT [Flight].[FlightNo], [Flight].[Depart], [Flight].[Destination], [NumSeats], SUM([NumSeats]*[SeatPrice]) AS TotalSales
FROM Flight LEFT JOIN Booking ON [Flight].[FlightNo]=[Booking].[Flight]
WHERE Flight.FlightNo NOT IN (Select Flight FROM Booking)
GROUP BY [Flight].[FlightNo], [Flight].[Depart], [Flight].[Destination]
HAVING [Flight].[Depart] = [Flight].[Destination];


regards,
hulan

[Roy Sinclair]

The links I find discussing that particular error message indicate several different possibilities. Here are some links to discussions that may offer you a solution:

http://www.tek-tips.com/gviewthread.cfm/pid/181/qid/405936

http://www.access-programmers.co.uk/forums/showthread.php?p=276018#post276018

http://www.utteraccess.com/forums/access/access383899.html

http://www.webfrustration.com/archive/105/2004/01/4/224935

[raf]

Your condition shoul also not be place in a havin-clause. It should be moved to the where-clause + ne of these fields (destination or departure) should be removed from the fieldlist since the values of both will be the same.
I also wonder if you realy need the outer-join...

What exactly are you trying to acchieve. I don't see the link between your initial post and the pseuocode inthere, and your query.
To get the number of flight per person, you only need

select count(*) as numberofflights, customer from booking group by customer order by numberofflights desc