Using a subquery to do a count limited by a where clause

1

Hello - Think I have a fairly simple use case … Want to know how many hosts (count) have a mean CPU over a certain value over the last 30 minutes. I can get the actual values fairly simply with a subquery (using 1.2.2) …

select mean from (select mean(usage_user) from cpu where time > now() - 30m GROUP BY host) where mean > 80

However, trying to get the count using the query below results in no data returned

select count(mean) from (select mean from (select mean(usage_user) from cpu where time > now() - 30m GROUP BY host) where mean > 80)

If I remove ‘where mean > 80’, it does work but that of course is not what I need. I also tried the following to no avail

select COUNT(“MEAN80”) from (select MEAN as MEAN80 from (select MEAN from (select mean(usage_user) as MEAN from cpu where time > now() - 30m GROUP BY host) where MEAN > 80))

Is there something wrong in my syntax? Or is this not supported?

2

That should be fixed on master/nightly: Refactor the subquery code and fix outer condition queries by jsternberg · Pull Request #8081 · influxdata/influxdb · GitHub

As far as I know, there aren’t plans to backport the fix to 1.2.

3

Thanks Mark! The nightly build does indeed fix that. When is 1.3 likely to be released?