Using GroupBy for "Count" and for "Total" in the same Table

grafana
influxdb

#1

Hey guys -

I’m currently running this query here to figure out the message set order when our service is not returning 200.

SELECT count("ui_status") From "crosslayer" WHERE $timeFilter AND ui_status != '200' Group By dmz1_mess

which ends up looking like this:

Time   |    Message set      |  Failures
---------------------------------------------------
XXX   |   message set 1     |     10
XXX   |   message set 2     |     60
XXX   |   message set 3     |     60

which is working great but I dont have any context to the rest of the data.

What I would like to see is how many times total the message set appeared in comparison to the count of the failures.

Time   |    Message set      |  Failures  |   Total 
-------------------------------------------------------------
XXX   |   message set 1     |      10     |   1000
XXX   |   message set 2     |      60     |     60
XXX   |   message set 3     |      60     |   5000

Is this particular example, I’m much more interested in digging into “message set 2”.

I tried doing two message set tables side by side, one for failures and one for total passed, but there are 100’s message sets and they are in a series of bits and they are pretty hard to compare.

Is there any thing I can do?

Thanks,
Nef


#2

So I actually made a little progress after reading some of the other “help” threads…

SELECT "Fails", "Total" From 
(SELECT count("ui_status") AS "Total" From "crosslayer"), 
(SELECT count("ui_status") AS "Fails" From "crosslayer" Where ui_status != '200') 
Where $timeFilter Group By dmz1_mess

but it returns this…

Time   |    Message set      |  Failures  |   Total 
-------------------------------------------------------------
XXX   |   message set 1     |      10     |      -
XXX   |   message set 2     |      60     |      -
XXX   |   message set 3     |      60     |      -
XXX   |   message set 1     |      -      |    1000
XXX   |   message set 2     |      -      |     60
XXX   |   message set 3     |      -      |    5000

I’m guessing it has something to do with the placement of Where $timeFilter Group By dmz1_mess

So how can how can I combine the columns?

Thanks,
Nef