Help with a count/sum query

InfluxDB 1 InfluxQL
, ,
1

Influx version 1.8.10

I have a slightly complicated query that groups by 4 different Tags, and then takes the last result from the grouping. For example, this query does exactly what I want - it returns the very last entry for the grouping if the grouping has failed.

select Test, Type, State, Result, Run, Error, ErrorStep, Info from (SELECT * from results where ENV='UAT' GROUP BY Test, Type, State, ENV ORDER BY time DESC limit 1) Where Result='Failed' GROUP BY Test, Type, State, ENV ORDER BY time DESC

image
image1599×172 32.1 KB

However, what I cant figure out is how to get the Count of the Rows of this Grouping -

select count(Code) from (SELECT * from results where ENV='UAT' GROUP BY Test, Type, State, ENV ORDER BY time DESC limit 1) Where Result='Failed' GROUP BY Test, Type, State, ENV ORDER BY time DESC

image
image1623×213 12.5 KB

And when I try to take the sum of the rows with something like this -

select sum(count) from (select count(Code) from (SELECT * from results where ENV='UAT' GROUP BY Test, Type, State, ENV ORDER BY time DESC limit 1) Where Result='Failed' GROUP BY Test, Type, State, ENV ORDER BY time DESC) GROUP BY count ORDER BY time DESC

I end up with 14 somehow -

image
image1597×147 3.99 KB

So my question is, how can I get the row count of the original query? -

select Test, Type, State, Result, Run, Error, ErrorStep, Info from (SELECT * from results where ENV='UAT' GROUP BY Test, Type, State, ENV ORDER BY time DESC limit 1) Where Result='Failed' GROUP BY Test, Type, State, ENV ORDER BY time DESC

Thank you for any and all help in advanced

2

sheepishly bumping this to see if anyone can help get the row count

3

I had a look at this but had no solutions…

The main problem is that you are actually limiting the dataset to only one row (as the first/inner query uses LIMIT 1), therefore a count should return only and always one row…

To get the count you can’t limit the number of rows, but by doing so you won’t be able to apply your multiple-cascade filter logic… (only latest row, then only failed)

You can try to work around it by using multiple queries and Grafana “Outer Join” transform (which is not an actual outer join but can do the trick in some cases)…
I don’t know if that’s feasible in your case since I don’t know what’s your dataset, data structure and what you expect in the different situations