Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

James Barrett
James Barrett
13,253 Points

How to retrieve the month with the least bookings? (SQL)

Hi there. I was wondering what SQL I would need to use find the month with the least bookings.

I have a Booking table:

https://i.gyazo.com/8994c79197dc3b44d0f4471a36aa1376.png

I have a Package table:

https://i.gyazo.com/6acbbd1b7b9aa73b43e7a46bbdb8de59.png

I have a HolidayMaker table:

https://i.gyazo.com/b3459cd20e4fc795c46305f357f6016e.png

I think this might have something to do with nested SELECT statements, however I am not entirely sure.

Thanks, James. :-)

2 Answers

anil rahman
anil rahman
7,786 Points

Probably your booking table will be the main focus as that has booking ID and the date, you can get months by using DATENAME(month, Bo_Datebooked) As MnthName. Then you would want to do a count by distinct booking ID's, something like this: COUNT(DISTINCT Booking_ID) AS countOfBooking. Then you want to group by the monthName.

I can't test this at the moment but i would try something like what i explained above which would look like this:

SELECT DATENAME(month, b.Bo_Datebooked) As MnthName
,MONTH(b.Bo_Datebooked) AS MonthNum
,COUNT(DISTINCT b.Booking_ID) AS countOfBooking

FROM Booking AS b

GROUP BY DATENAME(month, b.Bo_Datebooked)

ORDER BY MonthNum
anil rahman
anil rahman
7,786 Points

Sorry also to get the lowest booking month you use the MIN() function try using min on the count which you would need subquery for.

SELECT MIN(countOfBooking)
,MnthName
,MonthNum

FROM(

SELECT DATENAME(month, b.Bo_Datebooked) As MnthName
,MONTH(b.Bo_Datebooked) AS MonthNum
,COUNT(DISTINCT b.Booking_ID) AS countOfBooking

FROM Booking AS b

GROUP BY DATENAME(month, b.Bo_Datebooked)

--ORDER BY MonthNum
)sq

group by MnthName

order by MonthNum
Steven Parker
Steven Parker
220,416 Points

You didn't say what kind of database, and the syntax for parsing dates varies with the type of database engine.

But if this is for the SQL Playground, (or SQLite), you might want something like this:

SELECT strftime('%m', Bo_Datebooked) as Month
FROM Booking
GROUP BY strftime('%m', Bo_Datebooked)
ORDER BY count(*)
LIMIT 1

You might need to substitute for the strftime function (and maybe also LIMIT) if you have a different database.