SQL查询:奇怪的SUM行为

Question

我正在运行这个查询

SELECT events.*, venues.*,
SUM(events.eventCAPACITY) AS capacity,
SUM(CASE WHEN bookings.bookingAMOUNT is NULL THEN 0 ELSE bookings.bookingAMOUNT END) AS booked,
SUM(events.eventCAPACITY) - SUM(CASE WHEN bookings.bookingAMOUNT is NULL THEN 0 ELSE bookings.bookingAMOUNT END) AS available
FROM events
LEFT JOIN bookings ON events.eventID = bookings.bookingEVENT
LEFT JOIN venues ON venues.venueID = events.eventVENUE
WHERE events.eventDATE >= CURDATE()
GROUP BY events.eventID
ORDER BY events.eventDATE, events.eventTIME ASC

一切顺利。除了(通过另一个表(达到events.eventCAPITATION(例如20(时，它不会给出零，它只是再次给出events.eventCAPACITY中设置的数量(20(。

我做错了什么？

编辑

这是我运行查询得到的结果。

eventTIJDSTIP>eventCAPITATION><1th>已预订<2th>可用<1>11509:00:00<1><2>场馆1

eventID	eventDATUM	eventVENUE	venueID	venueNAME
1	20211-24	1	15	场馆1	15
2	2021-01-24	11:00:00	2	15	2	场馆2
3	2021-113	1	30	15	15

events.eventCAPACITY不需要SUM()。JOIN可能正在创建重复的值(如果要使用GROUP_CONCAT(events.eventCAPACITY，您可以看到这些值(。

这里的解决方案是在events.eventCAPACITY周围放置SUM()

SELECT events.*, venues.*,
events.eventCAPACITY AS capacity,
SUM(CASE WHEN bookings.bookingAMOUNT is NULL THEN 0 ELSE bookings.bookingAMOUNT END) AS booked,
events.eventCAPACITY - SUM(CASE WHEN bookings.bookingAMOUNT is NULL THEN 0 ELSE bookings.bookingAMOUNT END) AS available
FROM events
LEFT JOIN bookings ON events.eventID = bookings.bookingEVENT
LEFT JOIN venues ON venues.venueID = events.eventVENUE
WHERE events.eventDATE >= CURDATE()
GROUP BY events.eventID
ORDER BY events.eventDATE, events.eventTIME ASC