Sqlite query for multiple datasources

Grafana
1

  • What Grafana version and what operating system are you using?
    8.1.2 - Ubuntu

  • What are you trying to achieve?
    My goal is to achieve table view of my closed trades grouped by date…

  • How are you trying to achieve it?
    I made this query (sqlite as datasource).

SELECT
date(close_date) AS “Date”,
(SELECT COUNT() FROM trades WHERE close_profit > 0 AND date(c2.close_date) = date(close_date)) AS Wins,
(SELECT COUNT() FROM trades WHERE close_profit < 0 AND date(c2.close_date) = date(close_date)) AS Losses,
(SELECT AVG(close_profit * 100) FROM trades WHERE date(c2.close_date) = date(close_date)) AS “Avg”,
(SELECT SUM(close_profit_abs) FROM trades WHERE date(c2.close_date) = date(close_date)) AS “Profit”
FROM trades c2 WHERE date(close_date) IS NOT NULL GROUP BY date(close_date)

This will list dates (closed_date from table trades), Wins, Losses, Avg and Profit.
It works perfectly with one data source but I have six of them.
How do I have to change the query in a way that I can do use multiple datasources (mixed) at the same time ?

On my dashboard I have variable which lists all my sqlite datasources, but I use that as datasource with this particular query only the first sqlite db is used.

I can use the db selector from dashboard but this query only works with one db selected, if more than one is selected then only the first db selected is counted.

Any help is appcreciated… Thank you

2

Hi @parta

Can you include more details like your unformatted data and some screenshots with your query? This will help the community help you :+1:

3

Hi,

I actually was able to go further with this matter, but got stuck with another problem and I cannot figure out how to resolve it.

Now the query is looking like this:

SELECT *
FROM (

    SELECT 
           'Total' AS "Date",
           count(CASE WHEN close_profit > 0 THEN 1 END) AS Wins,
           count(CASE WHEN close_profit < 0 THEN 1 END) AS Losses,
           AVG(close_profit * 100) AS "Avg",
           SUM(close_profit_abs)  AS "Profit"
    FROM trades
    WHERE close_date IS NOT NULL 

    UNION ALL

    SELECT
           date(close_date) AS "Date",
           count(CASE WHEN close_profit > 0 THEN 1 END) AS Wins,
           count(CASE WHEN close_profit < 0 THEN 1 END) AS Losses,
           AVG(close_profit * 100) AS "Avg",
           SUM(close_profit_abs)  AS "Profit"
    FROM trades
    WHERE close_date IS NOT NULL 
    GROUP BY date(close_date)) AS sq

ORDER BY "Date" DESC;

This works great, exactly what Im looking for. And the outcome of this query is nice looking table:
1

Looks great, then I add more sources (two more) → this causes grafana to display results in different “response” views. I dont want separate response views so I have do transformation, and oblivious choise is to use Concenate Fields and outcome is this:

2
21298×265 17.6 KB

This is not the outcome I want so I have to use more Transformations.
Organize fields + group by → and get to this:

3
31483×774 40.1 KB

Next step would be group by “date”, and this works kinda but not exactly.
Idea is to group these different color boxes into one:

4
41476×364 19.6 KB

And if I group by “Date” this will happen but calculations are not correct…
This happens:

5
51476×364 20.6 KB

The rows from different responses get merged to wrong rows. Red arrow indicates where the merging happens (to the firtst row) and dotted green arrow indicates where the merge SHOULD happen (merge by Date).

6
61497×282 16 KB

How can I make sure that rows get merged by date and not just by the appearance order (top row gets merged to top row, second row gets merged to second row and so on ?

Of course after this “filter by name” has to be done and I can rename fields etc… But the main problem is the merging happening to wrong rows. How to prevent that ?

Any help is appreciated… Thanks

4

@parta have you tried the merge transformation?

5

As a general rule of thumb it’s worth remembering that the transformations in Grafana are there for convenience. They can perform SQL-lite pivots and transformations to your data, but it will always be more performant to structure your data and organize it a different way on the DB side :+1:

6

This topic was automatically closed after 365 days. New replies are no longer allowed.