Stacking categorical bars from different queries

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:

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:

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.

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?

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:

For the mysql query:

Not sure how to provide the data itself.

1 Like

Please post not as an image but as csv

A,b,c
1,2,3
"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

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

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)