CODEWITHSUNDEEP
sql
Phillip Gibson
Phillip Gibson

Reputation: 1555

sql query for hotel data base

I am attempting to answer the following question from a hotel database

Show the number of bookings, for each month of the 2011 calendar year.

This is what I have come up with so far. 

SELECT booking_no,
       date_in,
       Count(*)
FROM   booking
WHERE  date_in >= '01-jan-11'
       AND date_in <= '31-dec-11'
GROUP  BY booking_no,
          date_in
ORDER  BY date_in;

These are the database headings

GUEST

Name Type
--------------------- ------------
GUEST_NO 
SURNAME 
GIVEN 
DOB 
SEX 
HOME_PHONE 
WORK_PHONE 
FAX 
ADDRESS 
SUBURB 
STATE 
POSTCODE

ROOM

Name Type
--------------------- ------------
ROOM_NO 
FLOOR_NO 
SPA_BATH 
NUM_BEDS 
PRICE 
LINKED_ROOM_NO )

BOOKING

Name Type
--------------------- -----------
BOOKING_NO 
GUEST_NO 
ROOM_NO
DATE_IN 
DATE_OUT

Upvotes: 1

Views: 2988

Answers (3)

podiluska
podiluska

Reputation: 51494

You want the number per month, so

select month(date_in), count(*) 
from booking 
where year(date_in)=2011
group by month(date_in)
order by month(date_in);

Upvotes: 1

Nathan
Nathan

Reputation: 11149

What you need is a MONTH function. This is the same in most database engines. So try

SELECT booking_no, Month(date_in), Count(booking_no)

and

GROUP BY Month(date_in)
ORDER BY Month(date_in);

You could also simplify your where clause with the YEAR:

WHERE Year(date_in) = 2011

Altogether:

SELECT Month(date_in),
       Count(*)
FROM   booking
WHERE  Year(date_in) = 2011
GROUP  BY Month(date_in)
ORDER  BY Month(date_in);

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25337

Try his:

SELECT Count(booking_no) bookings,
       Month(date_in)    month_name
FROM   booking
WHERE  date_in >= '01-jan-11'
       AND date_in <= '31-dec-11'
GROUP  BY Month(date_in)
ORDER  BY Month(date_in);

Don't do "year(date_in)=2011" in the where clause, it will prevent the usage of an index, if you have one on the date_in column. If you don't have one, it doesn't matter.

Upvotes: 1

Related Questions