0

I want to get the sum of result_code where result_code=0.

What I found is that querying without sum gives me result but adding the aggregation sum() and then I have 0 as a result.

For those who want an understanding of the use case : I use telegraf to ping some internal IP address to check it's availability (pings every 10s with a timeout of 5s) and I want to get its uptime (result_code=0) per day.

I have tried changing the field in the sum function but that doesn't work better.

SELECT sum("result_code") FROM "ping" WHERE ("url" = 'MyUrl') AND time >= 1557957600000ms and result_code=0

name: ping
time                sum
----                ---
1557957600000000000 0

name: ping
time                result_code
----                -----------
1557988031000000000 0
1557988040000000000 0
...

In my example I expect to have

name: ping
time                sum
----                ---
1557957600000000000 2
Jérôme B
  • 311
  • 5
  • 18
  • 1
    Sum of zeroes is zero - what are you expecting to get? Looks like you need `count(result_code)` - not `sum()`. Also, you're using aggregating function without `group by time(1d)` in query. – Yuri Lachin May 16 '19 at 07:27
  • I meant `count` instead of `sum` thanks for pointing that out P.S. : using `group by time(1d)` gives me 2 rows (might be a problem with timezones...) – Jérôme B May 16 '19 at 07:44
  • To correct the timezone issue I used : `GROUP BY time(1d) , deviceId TZ('Europe/Paris')` – Jérôme B May 16 '19 at 08:00

1 Answers1

0

It sounds like you want COUNT, rather than the SUM of a collection of zero values.

Rawkode
  • 21,990
  • 5
  • 38
  • 45