I have this tables
device
id name groupId serviceId
791 Mamie Ortega 205 1832
group
id serviceId
205 1832
record
id date deviceId
792 2017-07-13 13:30:19.740360 784
793 2017-07-13 13:30:19.742799 784
alarms
id status deviceId
241 new 784
242 new 784
I'm running this query
SELECT device.id, device.name, COUNT(records.id) AS "last24HMessagesCount", COUNT(alarms.id) AS "activeAlarmsCount"
FROM device
INNER JOIN "group" AS "group" ON "device"."groupId" = "group"."id" AND "group"."id" = '205'
LEFT OUTER JOIN "record" AS "records" ON "device"."id" = "records"."deviceId" AND "records"."date" > '2017-07-12 11:43:02.838 +00:00'
LEFT OUTER JOIN "alarm" AS "alarms" ON "device"."id" = "alarms"."deviceId" AND "alarms"."status" = 'new'
WHERE "device"."serviceId" = 1832
GROUP BY device.id;
Which give me this result
id name last24HMessagesCount activeAlarmsCount
791 Mamie Ortega 4 4
This result is wrong, I'm supposed to have 2 for last24HMessagesCount and activeAlarmsCount.
If I remove one of the count, last24HMessagesCount for example and execute
SELECT device.id, device.name, COUNT(alarms.id) AS "activeAlarmsCount"
FROM device
INNER JOIN "group" AS "group" ON "device"."groupId" = "group"."id" AND "group"."id" = '205'
LEFT OUTER JOIN "alarm" AS "alarms" ON "device"."id" = "alarms"."deviceId" AND "alarms"."status" = 'new'
WHERE "device"."serviceId" = 1832
GROUP BY device.id;
The result is correct
id name activeAlarmsCount
791 Mamie Ortega 2
I do not understand, why are the counts double?