Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

James Barrett
James Barrett
13,253 Points
Posted by 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
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
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
229,732 Points
Steven Parker
Steven Parker
229,732 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.