Maximum number of SQL records with total value
Please consider the following two tables:
Holidays
HolidayID (PK)
Destination
Length
MaximumNumber
...
Bookings
BookingID (PK)
HolidayID (FK)
Name
...
Customers can book a vacation (like going to Hawaii). But let's suppose that this holiday has the maximum number of seats. for example this year there are only 75 holidays in Hawaii (ignoring other years).
So, if a client wants to book a Hawaii vacation. I need to count the entries in the Reservation table, and if that number is greater than 75, I have to tell the customer too late. I can do this using 2 MySQL queries (1 to get the MaximumNumber for a holiday, 2 to get the current amount from books) and PHP (for example) to compare the count against the maximum number of Hawaii holidays. But I want to know if there is a way to do this exclusively in SQL (MySQL in this case)? that is, count the number of orders for Hawaii and compare them to the Hawaiian MaximumNumber.
EDIT: My method:
$query1 = "SELECT MaximumNumber FROM Holidays WHERE HolidayID=$hawaiiID";
$query2 = "SELECT COUNT(BookingID) FROM Bookings WHERE HolidayID=$hawaiiID";
So, if the first request gives 75 and the second request gives 75, I can compare those values in PHP. But I was wondering if there is a way to do this somehow only in SQL.
I may be missing something, but why not use a subquery to determine the total orders for each holiday:
select *
from holidays h
left join
(
select count(*) TotalBooked, HolidayId
from bookings
group by holidayId
) b
on h.holidayId = b.holidayId
WHERE h.HolidayID=$hawaiiID;
See SQL Fiddle with Demo .
Then you can use an expression CASE
to compare TotalBooked
with MaxNumber
, similar to this:
select h.destination,
case
when b.totalbooked = h.maxNumber
then 'Not Available'
else 'You can still book' end Availability
from holidays h
left join
(
select count(*) TotalBooked, HolidayId
from bookings
group by holidayId
) b
on h.holidayId = b.holidayId
WHERE h.HolidayID=$hawaiiID;
See SQL Fiddle with Demo .
You will notice that I used LEFT JOIN
which will return all rows from the table Holidays
even if Bookings
there are no matching rows in the table .
source to share
You can try something like this:
select case when b.freq < h.MaximumNumber
then 'Available'
else 'Not Available'
end as MyResult
from Holidays h
left join (
select HolidayID
, count(*) as freq
from Bookings
where HolidayID=$hawaiiID
group by HolidayID
) b
on h.HolidayID=b.HolidayID
source to share