Why are the result of COUNT double when I do two join?

Viewed 3544

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?

2 Answers
Related