Different results for mysql count and sum queries in table panel

Grafana MySQL
,
1

  • What Grafana version and what operating system are you using?
    grafana 8.3.3 / windows 10 64

  • What are you trying to achieve?
    I’m trying to calculate the total number of records in a table panel with data from mysql

  • How are you trying to achieve it?
    To get the total number of records my query is something like SELECT COUNT(*) FROM table. The total number should be 1534 and the query result indeed is 1534.

In my panel i’m using variables to select certain records. To get the total of selected records i’m using a field holding only values of 1. So to get the number of selected records i’m using a query like SELECT sum(field_with_only_ones) FROM table.

  • What happened?
    What i don’t understand is that WITHOUT making any selection at all the sum of the query is 1529 and not 1534. So i’m missing 5 records.

  • What did you expect to happen?
    SELECT sum(field_with_only_ones) FROM table = SELECT COUNT(*) FROM table

Furthermore i noticed that there’s a difference between sum and SUM. For instance using SUM conditional coloring does not work, using sum it’s working fine.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    No.

  • Did you follow any online instructions? If so, what is the URL?
    No.

Anybody else having the same experience?

2

I think it would be helpful if you posted here the actual queries you are
performing, instead of “something like”…

The latter leaves us guessing as to what you are really doing, and that makes
it hard to identify what might be amiss.

Antony.

4

Apologies.

My queries:

Query using sum:

SELECT SUM(`Anomalie_model`)
FROM `anomalieen`
WHERE `Id` IN (${Id:csv}) AND `Boekingen_nr` IN (${Boekingnr:csv}) AND `Tijdstip` IN ($Tijdstip) AND `Docnr` IN (${Docnr:csv}) AND `Rgl` IN (${Rgl:csv}) AND `Relatienr` IN (${Relatienr:csv}) AND `Grootboek` IN (${Grootboek:csv}) AND `Kostenplaats` IN (${Kostenplaats:csv}) AND `Object` IN (${Object:csv}) AND `Project` IN (${Project:csv}) AND `Bedrag` IN (${Bedrag:csv}) AND `Score` IN (${Score:csv}) AND `Anomalie_status` IN (${Anomalie_status:csv}) AND `Anomalie_andere_audit` IN (${Anomalie_verschillende_audits:csv}) AND `Label_model` IN ($Label_model) AND `Anomalie_auditor` IN (${Anomalie_auditor:csv}) AND `Toelichting_auditor` IN ($Toelichting_auditor)

All values of the template variables: All (so no selection being made)
All field values in field Anomalie_model from table anomalieen: 1 (checked with mysql client / without Grafana)
Result from this query: 1529 records

Query using count:

SELECT COUNT(`Anomalie_model`)
FROM `anomalieen`

Result from this query: 1534 records

5

What do the following return?

Select count(Anomalie_model) from anomalien

Select count(0) from anomalien

Select count(Anomalie_model) from anomalien where Anomalie_model=1

Antony.

6

First of all: thank you for your support. Appreciate it.

Results from queries with count::

Select count(Anomalie_model) from anomalieen

1534 records

Select count(0) from anomalieen

1534 records

Select count(Anomalie_model) from anomalieen where Anomalie_model=1

1534 records

7

Okay, so you definitely get 1534 every time…

My next suggestion is either to:

  • edit the query in Grafana term by term, removing one instance of “AND
    Anomalie_status IN (${Anomalie_status:csv})” etc at a time, perform the
    query after removing it, and stop when you find the number changes from 1529 to
    1534

    or

  • copy the full query you’re using in Grafana into the MySQL client, and
    replace every instance of a template variable with “1”, and see whether you
    get 1529. If you do, perform the above “remove terms one by one” until the
    result changes to 1534.

I’m just thinking that the second option might be simpler and quicker to
achieve the same result.

Incidentally, the problem is not due to the term “AND Anomalie_andere_audit
IN (${Anomalie_verschillende_audits:csv})”, is it?

It’s the only condition where the database field name does not match the
Grafana template variable name…

Antony.

8

Hello Antony,

We’re getting warm!

Figured out that this part of my query is responsible for my missing records:

AND `Bedrag` IN (${Bedrag:csv})

Recently the only thing i changed in mysql is the length of the field ‘Bedrag’. I did some formatting with:

ALTER TABLE `anomalieen`   
  CHANGE `Bedrag` `Bedrag` DOUBLE(12,2) NULL;

Rolling back this change with:

ALTER TABLE `anomalieen`   
  CHANGE `Bedrag` `Bedrag` DOUBLE NULL;

results in the correct number of records.

Looks like Grafana does not handle summing correctly after field formatting

1 Like
9

I wonder whether this means that Grafana simply does not consider “1.00” to be
equivalent to “1”.

Still, as you say, at least you’ve found out where the problem lies - now just
to find a long-term solution…

Antony.

10

Antony,

Did some more research and it looks like records are not selected when three conditions are met:

  1. the field in mysql is DOUBLE
  2. the field in mysql contains values with more than 2 decimals
  3. in mysql the field type is changed from DOUBLE to DECIMAL.

When these three conditions are met Grafana is unable to select the corresponding records. Selecting both with and without template variables, so i don’t think it has anything to do with template variables.

It looks like mysql field type DECIMAL somehow is not (fully) supported in Grafana.