Avoid query duplications counter and table

I need to build a dashboard with a table and a counter.
The counter is based on the query result (with parameters so I cannot use the query based on another query).
Do I need to run the two almost identical queries twice (the counter, let’s say is a sum of a column)?
So the first query creates some table (with parameters).
The second query is "select sum(column_1) from (sub query identical to the query which created the main table).
Is there a way to avoid the duplicate queries?

The counter visualization settings includes a toggle for “Count Rows” specifically for this need.

I don’t want to count rows but to compute some stats on the table. E.g. sum x where y=5 .
It seems like I have to rerun the same query again to reach to these numbers independently from the table.

Then it sounds like you have two options:

  1. Run two similar queries.
  2. Use SQL window functions to calculate your statistics in one big query

Keep in mind that Redash doesn’t slice-and-dice data for you. Use your database for that. Redash gives you a convenient front-end for visualization and sharing the results. So if you need to perform two very different aggregations it makes sense you would need to run two different queries. This is by design :smiley:

@susodapop is correct that Redash’s general approach is that you use your DB yo slice-and-dice the data and we just present it. But over time we do look into adding convenience features, that either save work for the user or their database.

Of course we need to tread carefully here, but based on feedback we received having additional aggregate options except for count in the Counter visualization is desirable.

There is an open PR for this: #2381 from @tonyjiangh, but unfortunately after the React migration it’s no longer relevant. If someone interested in implementing this on top of the current React codebase, will be happy to accept.

We might want to consider renaming the visualization from Counter to something else after this, but it’s not a huge thing :slight_smile:

2 Likes

Isn’t the OP just asking for the Query Results datasource?

Write your first query, write your second query using the query results DS?

Or you could even use the Pivot Table vis.

From the OP’s post. Pivot table viz could also do the trick.

Yes, if it was possible to run a query on query with parameters it could be another solution to their problem. But the counter based solution might be simpler and faster to implement.

But a Pivot Table is not the same as a Counter in terms of visual result.

Ah; I see what you mean @arikfr.

Actually I quite like the idea of being able to include other queries - say put {{includeQuery:123}} in your query to have it include the contents of that query in the one you’re writing. The web UI would also have to pull any additional parameters from the included query into the new one.

I can see myself using that quite a lot, for parameter validation, and also for what the OP is proposing: have a query that creates a temporary table having extracted data using a variety of parameters; then several variants of how to pull results from that temporary table.

However… I’m not under the impression that there are that many people using Redash like I do; and I’m still not aware of any datasources other than MariaDB/MySQL that have this behaviour where you can execute a sequence of many many statements with one request.

I’ve never really looked at the query snippets stuff, but perhaps @chanansh might want to. I don’t think they’d be particularly great for my use-case (especially since if you edit the snippet then it only affects future uses of the snippet, not existing ones) but it may well satisfy his.