# Thread: Calculating Ave of Room type in Query using 1 table

1. Registered User
Join Date
Feb 2016
Location
Northpole
Posts
7

## Unanswered: Calculating Ave of Room type in Query using 1 table

Hi there

I am trying to calculate average length of stay per room type (Bay-window, Ocean, Side) in Query with all the data in one table. How can I find the average. In Query, I have selected Room, Room Type, Arrival Date & Departure date. Using Builder, what formula and how should I formulate.

My table includes the following columns:
ID
Guest First Name
Guest Last Name
Room
Room Type
Arrival Date
Departure Date
No of Guests
Daily Rate

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Code:
```SELECT AVG ([departure date]-[arrival date]) as StayLength, [room type] FROM [my table]
GROUP BY [room type]```
...place that in the SQL view of the query designer

A general comment column and table names should not use reserved words and or symbols. Because you have used a space (reserved symbol) that means affected columns / tables must be delimited with [ & ]

3. Registered User
Join Date
Feb 2016
Location
Northpole
Posts
7

## Thank you but it Didn't work

I tried this exactly, my table = Reservations, and I CAP the data. All in the SQL.

SELECT AVG ([Departure Date]-[Arrival Date]) as StayLength, [Room Type] FROM [Reservations]
GROUP BY [Room Type]

This is what is in my SQL right now:

SELECT Reservations.[Room Type], Reservations.[Departure Date], Reservations.[Arrival Date]
FROM Reservations
ORDER BY Reservations.[Room Type];
SELECT AVG ([Departure Date]-[Arrival Date]) as StayLength, [Room Type] FROM [Reservations]
GROUP BY [Room Type]

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
...so?
Thats two separate queries. Its not at all surprising 'it doesnt work'

How or where are you trying to use this?
What do you mean by 'CAP the data'

what happens if you run the query supplied but modified for your table/column names
Last edited by healdem; 02-11-16 at 06:48.

5. Registered User
Join Date
Feb 2016
Location
Northpole
Posts
7

## It worked

Thanks, I was working in a practicing query rather than opening a new one.

Can you help me out again,

I have to find Base income per room

I have attached the table picture here.

6. Registered User
Join Date
Feb 2016
Location
Northpole
Posts
7