Why is MySQL returning different counts for a query when I add in another element to a set - using FIND_IN_SET, GROUP BY, and COUNT(*)

Question

I have a MySQL query to get a summary of devices and their locations:

SELECT `devicetypes`.`permanentslug`,
   `devices`.`yearmanufactured`,
   `engagements`.`name` AS `location`,
   Count(*) AS `count`
FROM `devices`
   INNER JOIN `devicemodels`
           ON `devicemodels`.`id` = `devices`.`devicemodelid`
   INNER JOIN `devicetypes`
           ON `devicetypes`.`id` = `devicemodels`.`devicetypeid`
   INNER JOIN `locations`
           ON `locations`.`id` = `devices`.`locationid`
   INNER JOIN `engagements`
           ON `engagements`.`id` = `locations`.`engagementid`
WHERE  `devices`.`deletedat` IS NULL
   AND `devicemodels`.`deletedat` IS NULL
   AND `devicetypes`.`deletedat` IS NULL
   AND `locations`.`deletedat` IS NULL
   AND `engagements`.`deletedat` IS NULL
   AND Find_in_set(`locations`.`engagementid`, '224,286') > 0
GROUP  BY `devicetypes`.`permanentslug`,
      `locations`.`name`,
      `devices`.`yearmanufactured`
ORDER  BY `devicetypes`.`permanentslug` ASC,
      `engagements`.`name` ASC; 

When I run this, I get the following results:

permanentslug	yearManufactured	location	count
defibs	null	clinics	1
defibs	null	clinics	1
defibs	2012	clinics	1
defibs	2020	Hospital	2
defibs	null	Hospital	1

Which is kind of accurate, however it's odd that it isn't combining the top two lines and giving me a count of 2 for that row. But then, when I add another element into the array I'm checking with FIND_IN_SET, I get inaccurate results. Here's the new query that only adds 223 to the Find_in_set line:

SELECT `devicetypes`.`permanentslug`,
   `devices`.`yearmanufactured`,
   `engagements`.`name` AS `location`,
   Count(*) AS `count`
FROM `devices`
   INNER JOIN `devicemodels`
           ON `devicemodels`.`id` = `devices`.`devicemodelid`
   INNER JOIN `devicetypes`
           ON `devicetypes`.`id` = `devicemodels`.`devicetypeid`
   INNER JOIN `locations`
           ON `locations`.`id` = `devices`.`locationid`
   INNER JOIN `engagements`
           ON `engagements`.`id` = `locations`.`engagementid`
WHERE  `devices`.`deletedat` IS NULL
   AND `devicemodels`.`deletedat` IS NULL
   AND `devicetypes`.`deletedat` IS NULL
   AND `locations`.`deletedat` IS NULL
   AND `engagements`.`deletedat` IS NULL
   AND Find_in_set(`locations`.`engagementid`, '223,224,286') > 0
GROUP  BY `devicetypes`.`permanentslug`,
      `locations`.`name`,
      `devices`.`yearmanufactured`
ORDER  BY `devicetypes`.`permanentslug` ASC,
      `engagements`.`name` ASC; 

And the results yield:

permanentslug	yearManufactured	location	count
defibs	null	clinics	1
defibs	2012	clinics	1
defibs	2020	Hospital	2
defibs	null	Hospital	1
defibs	2015	Creekside	7
defibs	2019	Creekside	4
defibs	null	Creekside	9

So, now it's completely removing one of the null clinics rows, but it's not combining it's count together with the other row. Very strange overall. Is this maybe an issue with FIND_IN_SET ? I've never seen MySQL miscount like this before.

Answer 1

I knew this had to be some silly mistake in my SQL query and sure enough it was. The GROUP BY at the bottom of the query is grouping by locations.name however I'm then displaying engagements.name AS location in my results.

When I changed the GROUP BY to be

GROUP BY DeviceTypes.permanentslug, Engagements.name, Devices.yearManufactured

it fixed everything. Hopefully nobody spent the time reading all of this before I figured it out.