Grouping data in a table by conditions

Grafana Dashboards
, , ,
1

I have a table that has Ages of people and the Count of people with that age. I want to simplify the table by creating age ranges (e.g. 0-4, 4-12, 13-18, etc.). I have played around with a few different transformations within Grafana, but none of them worked. I am not able to change the query for the data at all.

2

You might want to check out the new SQL expression to group by ranges

3

I’m not using a SQL database. I am using Elastic Search with Lucene queries

4

See this

5

OH, that’s very cool. Thank you. I’ll read through it. Quick question. Do you know if you can use that to get data after a transformation already? And then do more transformations after the SQL expressions.

6

My method is never ever use transformation unless the datasource does not provide a means to get what you need.

Sql expression is pretty much the answer to transformations

image
image972×591 44.9 KB

image
image1107×420 13.3 KB

7

Thank you very much. I am running into a small issue when I try to do it. My field name for age is ‘tag.age.keyword’ and when I try to put that in SQL I am getting an error, I am going to read up on SQL and see if/how I can fix this, but if you know the answer and can help me that’d be great.

EDIT: Got it, nvm, thanks

8

Thank you very much. I am running into a small issue when I try to do it. My field name for age is ‘tag.age.keyword’ and when I try to put that in SQL I am getting an error, I am going to read up on SQL and see if/how I can fix this, but if you know the answer and can help me that’d be great.

EDIT: Got it, nvm, thanks

EDIT2: I am running into this error now `[sse.sql.input_limit_exceeded] sql expression [F] was not run because the number of input cells (columns*rows) to the sql expression exceeded the configured limit of 100000` do you know how to fix this?

9

What does your sql query look like?

10 

SELECT COUNT(*) AS how_many_peeps,
    CASE
      WHEN age = '4_12' THEN '4_12'
      WHEN age = '13_18' THEN '13_18'
      WHEN age = '19_30' THEN '19_30'
      WHEN age = '31_50' THEN '31_50'
      WHEN age = '50+' THEN '50+'
      WHEN age BETWEEN 0 AND 3 THEN '0_3'
      WHEN age BETWEEN 4 AND 12 THEN '4_12'
      WHEN age BETWEEN 13 AND 18 THEN '13_18'
      WHEN age BETWEEN 19 AND 30 THEN '19_30'
      WHEN age BETWEEN 31 AND 50 THEN '31_50'
      WHEN age > 50 THEN '50+'
     ELSE age END AS age_group
FROM (
    SELECT `tags_object.age.keyword` AS age FROM A
    UNION ALL
    SELECT `tags_object.age_2.keyword` AS age FROM B
) combined
GROUP BY age_group;
11
  1. Remove Union all
  2. Add LIMIT 100 at the end of the query for test purposes
12

It works with just A. The issue is that I am using it on a VERY large table (~50,000 rows and 3 columns) that just happens to be split into a few smaller ones. So if I run it on each smaller one first and then combine them it works. But if I combine first then I get that error since there are more than 100,000 cells. The question is where/how to change that 100,000 limit.

1 Like
13

Probably cannot. Why would you need 100k

14

Because that’s what my data is in total. 500k is actually just a small part of my database. The whole database in Elasticsearch has over 150M documents.

15

I would file a bug for this ghat is still in Preview

1 Like