How to retrieve the month with the least amount of bookings in MySQL?

Question

I working on a little web project and I was wondering what SQL I would need to use find the month with the least amount of bookings.

I have a Booking table:

I have a Package table:

I have a HolidayMaker table:

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

Thanks, James. :-)

Step 1, construct a (JOIN) query which returns the bookings with months and their amounts. (GROUP BY perhaps.) Step 2, pick the month with the least amount of bookings. — jarlh, Apr 19 '16 at 09:57
"least amount" is this "amount" the number of bookings or by £value? — Matt, Apr 19 '16 at 10:04
what do you mean by "the least amount of bookings" ? are you looking for the least number of booking count or least amount of package price in a month ? — Md. Mostafizur Rahman, Apr 19 '16 at 10:42
@Md.MostafizurRahman Sorry for the vague question, it's basically to check how many bookings there are in each month, money will not come into this equation :) — James Barrett, Apr 19 '16 at 10:46
@Matt Sorry for the vague question, it's basically to check how many bookings there are in each month, money will not come into this equation :) — James Barrett, Apr 19 '16 at 10:47
@JamesBarrett, now clear your problem, you can check my answer. hope it will help you. if does not work then just find these syntax for mysql. i tested my query in my MS SQL Server. — Md. Mostafizur Rahman, Apr 19 '16 at 11:25

score 2 · Answer 1 · answered Apr 19 '16 at 10:07

2

Use MONTHNAME and GROUP BY a COUNT.

SELECT MONTHNAME(Bo_Datebooked), COUNT(Booking_ID)
FROM Booking
GROUP BY MONTHNAME(Bo_Datebooked)
ORDER BY COUNT(Booking_ID) ASC

answered Apr 19 '16 at 10:07

Matt

14,906
27
99
149

score 2 · Answer 2 · answered Apr 19 '16 at 10:10

The following query returns the booking amount for each month:

SELECT   DATE_FORMAT(Bo_DateBooked, '%Y-%m') AS BookingMonth,
         SUM(Package_Price)                  AS MonthlyAmount
FROM     Booking
JOIN     Package
  ON     Booking.Package_ID = Package.Package_ID
GROUP BY DATE_FORMAT(Bo_DateBooked, '%Y-%m');

Using a nested SELECT, you can get the minimum.

score 1 · Answer 3 · answered Apr 19 '16 at 10:06

1

This is just an aggregation with limit:

select date_format(bo_datebooked, '%Y-%m') as yyyymm, count(*)
from booking b
group by date_format(bo_datebooked, '%Y-%m') 
order by count(*) desc
limit 1;

answered Apr 19 '16 at 10:06

Gordon Linoff

1,242,037
58
646
786

MarcM · Accepted Answer · 2016-04-19T10:47:38.327

In case you are interested in seeing results 'only' for months, not taking in account years: (I.e. All reservations from any January, no matter what year):

SELECT MONTHNAME(Bo_Datebooked) as month, COUNT(1) as num_reservations
FROM Booking
GROUP BY month
ORDER BY num_reservations ASC
/* additionality add... 
/* LIMIT 1 */
/* ...to see only the lower result */

In case you're concerned in differenciate months from each year (I.e: reseravations from January 2016, reservations from January 2012...):

SELECT CONCAT(MONTHNAME(Bo_Datebooked), YEAR(Bo_Datebooked)) as date, COUNT(1) as num_reservations
FROM Booking
GROUP BY date
ORDER BY num_reservations ASC
/* additionality add... 
/* LIMIT 1 */
/* ...to see only the lower result */

Warning: any of this won't show you months with 0 reservations!!!

COUNT(1) counts the number of rows that matches a specified criteria. In this case, in a SELECT with a GROUP BY, counts the items for each month (or month-year). It works the same as COUNT(\*). Details about COUNT() function here: http://www.w3schools.com/sql/sql_func_count.asp Details about COUNT(1) vs COUNT(\*) here: http://stackoverflow.com/questions/1221559/count-vs-count1 — MarcM, Apr 19 '16 at 11:08

score 0 · Answer 5 · answered Apr 19 '16 at 11:10

You can use this sql. I test this in my ms sql server. If it's not working just find similar syntax for mysql server. ok ?

SELECT CONVERT(CHAR(3), DATENAME(MONTH, Bo_Datebooked)), COUNT(Booking_Id) FROM Booking
GROUP BY CONVERT(CHAR(3), DATENAME(MONTH, Bo_Datebooked))
ORDER BY COUNT(Booking_Id) ASC

Hope this will help you

How to retrieve the month with the least amount of bookings in MySQL?

5 Answers5