How to update Query to calc SUM or COUNT using two dates and BETWEEN?

My current query is as such:

     SELECT
      CAST(COUNT(`MID`) AS UNSIGNED) AS Y,
      CONCAT(
          LEFT(MONTHNAME(`date`),
          3),
          ' ',
          YEAR(`date`)
      ) AS label
  FROM
      `reservations`
  WHERE
      `MID` = 22 AND YEAR(`date`) = YEAR(CURDATE())
  GROUP BY
      CONCAT(
          LEFT(MONTHNAME(DATE),
          3),
          ' ',
          YEAR(`date`)
      ),
      YEAR(DATE),
      MONTH(DATE)
  ORDER BY
      YEAR(`date`),
      MONTH(`date`) ASC

It produces the following results that we use in Google Charts to show the number of reservations per month. The issue is we are only getting the number of times a reservation is created, not the number of days between the start date (date) and the end date (dateLast).

 Y      label
 ________________
 22      Feb 2019
 28      Mar 2019
 15      Apr 2019
 3       May 2019
 5       Jun 2019
 2       Jul 2019
 1       Aug 2019
 1       Oct 2019
 2       Nov 2019
 9       Dec 2019

I’ve been trying the following update but getting an error related to the BETWEEN operator:

 SELECT
     CAST(COUNT(`mid`) AS UNSIGNED BETWEEN `date` AND `dateLast`) AS D, CONCAT(
         LEFT(MONTHNAME(DATE),
         3), ' ',  YEAR(DATE) ),
     CAST(COUNT(`mid`) AS UNSIGNED) AS Y,
     CONCAT(
         LEFT(MONTHNAME(DATE),
         3),
         ' ',
         YEAR(DATE)
     ) AS label
 FROM
     `reservations`
 WHERE
     `mid` = 22 AND YEAR(DATE) = YEAR(CURDATE())
 GROUP BY
     CONCAT(
         LEFT(MONTHNAME(DATE),
         3),
         ' ',
         YEAR(DATE)
     ),
     YEAR(DATE),
     MONTH(DATE)
 ORDER BY
     YEAR(DATE),
     MONTH(DATE) ASC

MySQL said: Documentation

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘BETWEEN date AND dateLast) AS D, CONCAT(LEFT(MONTHNAME(DATE),’ at line 2

The goal is to get a SUM for all days reserved between AND including date to dateLast Note: dateLast is not counted as it is the Checkout date. Maybe this is too complex for a SQL query and should be handled in PHP as a series of sub-routines?

Answer

If you don’t need to split the number of days of one reservation over multiple months, then you can just use SUM(DATEDIFF(dateLast, date))

select year(date) y, month(date) m, sum(datediff(dateLast, date)) c
from reservations
group by y, m
order by y, m

db-fiddle

If you want to split them, then I hope your version (MySQL 8+ or MariaDB 10.2+) supports recursive queries. In that case you can expand the date range to one row per day in the range and count them:

with recursive rcte as (
  select date, dateLast 
  from reservations
  where dateLast > date -- optional
  union all
  select rcte.date + interval 1 day, rcte.dateLast
  from rcte
  where rcte.date < rcte.dateLast - interval 1 day
)
  select year(date) y, month(date) m, count(*) c
  from rcte
  group by y,m
  order by y,m

db-fiddle