Aggregated query returning 1s where 0's are expected

52
June 09, 2021, at 2:00 PM

In my devices table, is_muted is 0 for all 4 rows:

yet when I run the following query:

SELECT
    devices.id,
    devices.hostname,
    devices.last_seen_at,
    devices.is_muted,
    SUM(CASE WHEN events.is_healthy = 0 THEN 1 ELSE 0 END) AS num_checks_unhealthy
FROM devices
LEFT JOIN events ON events.devices_id = devices.id
GROUP BY devices.id

it returns is_muted as 1 for all devices.

Yet if I remove devices.hostname from the query, is_muted returns correctly:

SELECT
    devices.id,
    devices.is_muted,
    SUM(CASE WHEN events.is_healthy = 0 THEN 1 ELSE 0 END) AS num_checks_unhealthy
FROM devices
LEFT JOIN events ON events.devices_id = devices.id
GROUP BY devices.id

or if I leave devices.hostname in the SELECT but remove the JOIN, it also returns correctly:

SELECT
    devices.id,
    devices.hostname,
    devices.is_muted
FROM devices
GROUP BY devices.id

Can someone please help me figure out what am I misunderstanding? Why does is_muted return 1 rather than 0 when I select another random TEXT column from the same table, or use a JOIN? Where is the 1 coming if all values in the is_muted column are 0?

Update

Selecting is_muted in an IF statement is returning the expected data:

SELECT 
  devices.id,
  devices.hostname,
  devices.last_seen_at,
  IF(devices.is_muted = 1, TRUE, FALSE) AS muted,
sum(case when events.is_healthy = 0 then 1 else 0 end) AS num_checks_unhealthy,
sum(case when events.is_healthy = 1 then 1 else 0 end) AS num_checks_healthy
FROM devices
LEFT JOIN events ON events.devices_id = devices.id
GROUP BY devices.id

I tested this by muting one device and verified the response:

READ ALSO
Parse an HTML string with JS

Parse an HTML string with JS

I searched for a solution but nothing was relevant, so here is my problem:

36
Splitting the text and writing to combo box

Splitting the text and writing to combo box

It's only writes to comboBox1What's the problem? Why doesn't it write to other combo boxes?

31
Complex RegEx pattern Extractor Logic [closed]

Complex RegEx pattern Extractor Logic [closed]

Want to improve this question? Update the question so it focuses on one problem only by editing this post

63
Problem with getting JSON to android app, propably problem with url

Problem with getting JSON to android app, propably problem with url

I think i did something wrong with string named "url", but im not sure what should i change, what am i doing wrong?

47