How rename legend adding value from query from another column?

Grafana Dashboards
1

Hello everyone. I try create a dashboard(pie chart) with query to PostgresQL. I get simple table with tree columns

> select 
>         distinct public.message.model Model,
>         public.message.rating Rating,
>         count(*) "Total messages"
>     from
>         public.message
>         where public.message.model <> '' and public.message.model = 'GPT-3'
>         group by
>             Rating, Model
>                 order by Model
> ;

The result of query is the table:
**Model:      Rating:     Total messages:**
 GPT-3          0               20       
 GPT-3         -1               20       
 GPT-3          1               500

Dashboard settings

Value option set:

Show: “All values”
Fields: “Total messages”

Legend:

Legend values: “persent”, “value”

I get output legend like:

                                                value  persent                                      
* (some color)          GPT-3 Total messages     500    96%,
* (other color)         GPT-3 Total messages     20      4%
* (some other color)    GPT-3 Total messages     20    4%

But I want show:
value persent

  • (some color) GPT-3 without rating Total messages 500 96%,
  • (other color) GPT-3 positive Total messages 20 4%
  • (some other color) GPT-3 negative Total messages 20 4%

In DB table the field describe rating has numeric type: -1 value is negative, 1 is positive, 0 without rating

2

I would say this is not “how to rename”, but how to write query for that.

I would use CASE expression. Snippet to give you idea (not real copy&paste SQL, so improve it for your need and fix any syntax errors):

SELECT
  CASE 
    WHEN public.message.model='GPT-3' AND public.message.rating = 0 THEN 'GPT-3 with rating 0'
    WHEN public.message.model='GPT-3' AND public.message.rating = 1 THEN 'GPT-3 with rating 1'
    [WHEN ...]
    [ELSE 'uknown']
  END AS model,
  COUNT(*) AS "Total messages"
FROM
...
2 Likes
3

do you have models other than gpt3?

4

Yes, I have other

5

Of course that could be solved by Grafana transformations (see very dirty example below), but it’s better to rewrite the query as @jangaraj suggested.

transformations
transformations1079Ă—469 36.6 KB

1 Like
6

Thank you very very much)) but I am going ask more(can be stupid) questions later. Sorry I am not familiar with Grafana)

7

then go with what @jangaraj recommended but make the model column name dynamic

when public.message.rating = 0 THEN concat( public.message.model,’ with rating 0’)

type of thing

1 Like
8

image
image1418Ă—481 39.9 KB

;with src
as
(
select 'GPT-3' model, 0 as rating union all
select 'GPT-3' model, 1 as rating union all
select 'GPT-3' model, 1 as rating union all
select 'GPT-3' model, 1 as rating union all
select 'GPT-3' model, 1 as rating union all
select 'GPT-4' model, 1 as rating union all
select 'GPT-5' model, 0 as rating union all
select 'GPT-3' model, null as rating 
)
SELECT
  CASE 
    WHEN rating = 0 THEN concat(model, ' with rating 0')
    WHEN rating = 1 THEN concat(model,' with rating 1')
    ELSE 'Unknown'
  END AS model,
  COUNT(1) AS "Total messages"
FROM src
group by rating, model
1 Like