Stacking categorical bars from different queries

Grafana Dashboards
1

My data consists of distances driven in different areas (urban, highway, …) and also planned values for each of these categories. My goal is to visualize them in a bar chart, with the categories on the x axis, the distances on the y axis, and two bars for each category, the actual and the planned value. Similar to this:

image
image1117×546 12 KB

Even better if the bars are stacked, e.g. the planned value as an unfilled bar with dashed outline for example.

However, the actual distances are aggregated from an InfluxDB, while the planned data is stored in a MySQL DB, so they have to be queried separately.
Here are the queries, visualized as bars next to each other:

image
image1961×1063 122 KB

The first four bars are the actual, the last the planned values.
The corresponding bars/fields(?) have the same name, but how do I “join” them?

Thanks for any help in advance.

2

welcome

what are the names of the common fields?
Could you please provide sample data for both queries?

Also why do you have the format of the queries as time series?

3

I don’t have to format the query as time series. Here is the data in table view with table formatting.

For the influx query:

image
image1288×622 49.7 KB

For the mysql query:

image
image1268×624 48.9 KB

Not sure how to provide the data itself.

1 Like
4

Please post not as an image but as csv

A,b,c
1,2,3
5 
"Time","road_type","sum"
2022-03-14 01:00:00.000,highway,30342
2022-03-14 01:00:00.000,mountain,673
2022-03-14 01:00:00.000,rural,17927
2022-03-14 01:00:00.000,urban,11568

and

"startDate","highway","mountain","urban","rural"
2022-03-14 01:00:00,50000,10000,20000,30000
1 Like
6

Let me try to sort this out.

probably this might be a good time to switch to flux query language if you are able to as it allows you to query non influxdb data.

1 Like
7

ok, looks like you will have to pivot either the influx or unpivot mysql query at source.
currently the only column name that matche are the date/time columns

right now mysql is pivoted whereas influx is not

also the mysql table design is a bit suspect, you have a literal road type column names: highway, urban etc.

What happens when we have flying cars (hyperbole)