SQL Count: erratic behaviour

A piece of SQL that I have written is not behaving as intended. A vital piece of logic involves counting how many guests are VIPs, but the SQL seems to consistently get an incorrect answer.

The following database has 6 guests, 3 of whom are VIPs.

CREATE TABLE `guest` (
  `GuestID` int(11) NOT NULL DEFAULT '0',
  `fullname` varchar(255) DEFAULT NULL,
  `vip` tinyint(1) DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `guest`
--

INSERT INTO `guest` (`GuestID`, `fullname`, `vip`) VALUES
(912, 'Sam',  0),
(321, 'Sev', 0),
(629, 'Joe', 0),
(103, 'Tom', 1),
(331, 'Cao', 1),
(526, 'Conor', 1);

Initially the SQL returned a value saying that there were 5 VIPs, which is incorrect as there are only 3 VIPs. This is quite a complicated database, and in generating a minimum viable example for the sake of this question (with a reproducible error) the script now states that there are only 2 VIPs. Again, this is incorrect.

The SQL in question is

SELECT slotguest.FK_SlotNo, Count(CASE WHEN guest.vip = 1 THEN 1 END) AS guest_count 
FROM guest 
INNER JOIN slotguest ON guest.GuestID = slotguest.FK_guest 
GROUP BY slotguest.FK_SlotNo;

The slotguest structure and content is as follows

CREATE TABLE `slotguest` (
  `FK_SlotNo` int(11) NOT NULL,
  `FK_guest` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `slotguest`
--

INSERT INTO `slotguest` (`FK_SlotNo`, `FK_guest`) VALUES
(396, 912),
(396, 321),
(396, 629),
(396, 103),
(396, 331),
(396, 526);

What is causing Count to come up with a consistently incorrect answer?

Answer

As identicated in the comments (check from users @Fábio Amorim, @Rajat), your query seems to work as intended. Since, you set a value with the CASE WHEN, it might be better to use SUM.

It might be more visible if you bring the counts for the different VIP categories to find where there might be a leakage of data.

SELECT guest.vip, slotguest.FK_SlotNo, COUNT(*) AS guest_per_category
FROM guest 
INNER JOIN slotguest ON guest.GuestID = slotguest.FK_guest
GROUP BY guest.vip,slotguest.FK_SlotNo;

Advertisement

Answer