mysql - 按月查找MySQL中的条目数

我试图按月查找MySQL中的总数：

这是我的代码：

SELECT DATE_FORMAT(bookingdatetime, '%m/%y') `mmyy`,
       SUM(class IN ('local')) and (status IN ('Open', 'Closed', 'Confirmed')) `fulfilledlocal`,
       SUM(class IN ('local')) and (status IN ('Canceled', 'Unconfirmed', 'Communicating')) `unfulfilledlocal`,
       SUM(class IN ('local')) `alllocal`
FROM `reservations`
GROUP BY DATE_FORMAT(bookingdatetime, '%m/%y'), YEAR(bookingdatetime)
ORDER BY YEAR(bookingdatetime), DATE_FORMAT(bookingdatetime, '%m/%y')

alllocal结果正常工作，但是当我尝试获取满额本地和未满额本地时，它只给我第一个，而不是全部三个。

编辑：

我的reservations表中有三列；

+-----------------+------------------+-------+
| bookingdatetime |    status        | class |
+-----------------+------------------+-------+
|                 | *possible values |       |
|                 | Open             |       |
|                 | Closed           |       |
|                 | Confirmed        |       |
|                 | Canceled         |       |
|                 | Unconfirmed      |       |
|                 | Communicating    |       |
+-----------------+------------------+-------+

最佳答案

您需要将AND条件放在SUM()的参数内。

SUM(class IN ('local') and status IN ('Open', 'Closed', 'Confirmed')) `fulfilledlocal`,
SUM(class IN ('local') and status IN ('Canceled', 'Unconfirmed', 'Communicating')) `unfulfilledlocal`,