MySQL count() within subquery

Question

I am trying to count the number of bookings for the next 7 days with the following query.

select calendarDate, 
   (
    select COUNT(*)
    FROM isBooked INNER JOIN booking
    ON isbooked.BookingID = booking.bookingID
    where specificday between booking.startDate and booking.endDate
    ) 
from calendar as specificday
where calendardate between  '2015-08-23' and DATE_ADD('2015-08-23', INTERVAL 6 DAY);

I have used SQL server which allows the use of 'as specificday' however MySQL does not, how would i rewrite the query in mysql.

Gordon Linoff · Accepted Answer · 2015-08-23 14:24:01Z

1

specificday refers to a table, not a column. You need a column name for the WHERE clause:

select c.calendarDate, 
       (select COUNT(*)
        from isBooked ib INNER JOIN
             booking b
             ON ib.BookingID = b.bookingID
        where c.calendarDate between b.startDate and b.endDate
       ) 
from calendar c
where c.calendardate between '2015-08-23' and DATE_ADD('2015-08-23', INTERVAL 6 DAY);

edited Aug 23, 2015 at 14:24

answered Aug 23, 2015 at 14:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL count() within subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related