Bar Guage & MySQL Basic question

Grafana MySQL
, ,
1

Environment:
Grafana 7.3.7
Database - MySQL (MariaDB) 10.x

Question
I am trying to do a very simple bar guage (or even table is fine) visualization of a simple data structure. I have an Orders table and I would like to display the total number of orders for each user in the given time frame.

Data example:
id | customer | created_on_formatted

1 | customer1 | 11-1-2022
2 | customer2 | 11-1-2022
3 | customer1 | 11-2-2022
4 | customer3 | 11-2-2022
…
I am expecting the Bar Guage to count the number of orders Per customer and display:
customer1 - 2
customer2 - 1
customer3 - 1

I’ve tried the following but am getting a SQL error:

SELECT
  customer as "metric",
  count(customer) as "value"
FROM view_orders
WHERE
  $__timeFilter(created_on_formatted)

Any help would be appreciated!

2

Welcome

Would be nice if you could show us the error?

4

I put the generated query in MySQL and found it was missing GROUP BY and then on the updated query Grafana complained it was missing the time field. Here is my updated query:

SELECT
  customer as "metric",
  count(customer) as "value",
  created_on_formatted as "time"
FROM view_orders
WHERE
  $__timeFilter(created_on_formatted)
GROUP BY customer

Grafana is throwing the error “Query failed. Please inspect Grafana server log for details”.
The generated query in MySQL throws this error:

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'view_orders.customer'; this is incompatible with sql_mode=only_full_group_by

1 Like
5

Below the query box select table rather than Time Series