Hey,
sorry for the beginner question.
Can you point me to the resource, where I can read what I need to know. I dont even know how to describe my problem in a short “google term”.
I lead a gamegroup and would like to make a line graph out of our scores. The “weekly” scores are summed up each semester and I would love to have something like this, but interactive for all players to see. Reddit - Dive into anything
I already got a working MariaDB and I inserted the first fake-data.
SELECT * FROM score;
+----+-----------+--------+------------+
| id | player | points | date |
+----+-----------+--------+------------+
| 1 | Spieler 1 | 100 | 2024-06-03 |
| 2 | Spieler 2 | 110 | 2024-06-03 |
| 3 | Spieler 3 | 90 | 2024-06-03 |
| 4 | Spieler 4 | 95 | 2024-06-03 |
| 5 | Spieler 5 | 105 | 2024-06-03 |
| 6 | Spieler 6 | 98 | 2024-06-03 |
| 7 | Spieler 7 | 88 | 2024-06-03 |
| 8 | Spieler 8 | 102 | 2024-06-03 |
| 9 | Spieler 1 | 105 | 2024-06-10 |
| 10 | Spieler 2 | 95 | 2024-06-10 |
| 11 | Spieler 3 | 85 | 2024-06-10 |
| 12 | Spieler 4 | 100 | 2024-06-10 |
| 13 | Spieler 5 | 110 | 2024-06-10 |
| 14 | Spieler 6 | 92 | 2024-06-10 |
| 15 | Spieler 7 | 85 | 2024-06-10 |
| 16 | Spieler 8 | 108 | 2024-06-10 |
+----+-----------+--------+------------+
Can you help me please and show me a tutorial or something so I can finish it?
Best wishes
Thank your for your help.
Do I need to make the third column in a special format? I just pasted this into the MariaDB command shell
INSERT INTO score (player, points, date) VALUES (‘Spieler 1’, 105, ‘2024-06-10’), […]
Maybe thats why it does not recognize the time.
And I would like to display the sum of points.
So in this example player 1 (spieler 1) hast 100 points on the 2024-06-03 and 205 on 2024-06-10
Player 2 110, 205
Player 3 90, 175 and so on. But I think I already found the SUM command.
EDIT:
I tried to make it better and nuked everything. Ill test it tomorrow again. Maybe ill delete all data again. I tried to rename everything in case my german name (which I replaced just for this post) screwed something up.
Thank you and Ill come back, if its close to working again
it should be a datetime data type column that takes UTC datetime.
Okay. Ill get there soon. Thanks for helping me.
I translated everything now, so I dont make any mistakes there anymore.
I granted all “PRIVILEGES” to my user and now it also works. I checked 
This is my console. Database called dk06 (6th iteration )
MariaDB [dk06]> SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dk06' AND TABLE_NAME = 'score'
-> ;
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| id | int |
| player | varchar |
| points | int |
| date | datetime |
+-------------+-----------+
date is now datetime!!!
Still get this error with your code.
But in the “Builder section” I already got this.
So it just need to understand the concept of time. It already adds the correct players , but time is difficult
EDIT:
With these data points
MariaDB [dk06]> Select * from score;
+----+-----------+--------+---------------------+
| id | player | points | date |
+----+-----------+--------+---------------------+
| 1 | Spieler 1 | 100 | 2024-06-04 11:55:35 |
| 2 | Spieler 2 | 110 | 2024-06-04 11:55:35 |
| 3 | Spieler 3 | 90 | 2024-06-04 11:55:35 |
| 4 | Spieler 4 | 95 | 2024-06-04 11:55:35 |
| 5 | Spieler 5 | 105 | 2024-06-04 11:55:35 |
| 6 | Spieler 6 | 98 | 2024-06-04 11:55:35 |
| 7 | Spieler 7 | 88 | 2024-06-04 11:55:35 |
| 8 | Spieler 8 | 102 | 2024-06-04 11:55:35 |
| 9 | Spieler 1 | 100 | 2024-05-25 00:00:00 |
| 10 | Spieler 2 | 110 | 2024-05-25 00:00:00 |
| 11 | Spieler 3 | 90 | 2024-05-25 00:00:00 |
| 12 | Spieler 4 | 95 | 2024-05-25 00:00:00 |
| 13 | Spieler 5 | 105 | 2024-05-25 00:00:00 |
| 14 | Spieler 6 | 98 | 2024-05-25 00:00:00 |
| 15 | Spieler 7 | 88 | 2024-05-25 00:00:00 |
| 16 | Spieler 8 | 102 | 2024-05-25 00:00:00 |
+----+-----------+--------+---------------------+
16 rows in set (0.000 sec)
MariaDB [dk06]>
what if you did date as time
Thats almost it Looks almost like the thing I want.
Man im super hyped.
Sorry for spamming this, its just way over my head.
Now I just need to draw lines for each individual player. And add the daily points for each player. Time Series is not working with your code
Got all data points and they seem to be correct.
I edited my graph with my codebuilder and I got this far
SELECT player AS "metric", date AS "time", SUM(points) FROM dk06.score GROUP BY date, player
This is almost done. Here I just need the “SUM” function to work.
For an easyier dataset.
Player 1, 2 points, 01.01
Player 2, 3 points, 01.01
Player 1, 5 points, 02.01
Player 2, -1 point, 02.01
I would like to draw a line for
Player 1: 01.01 value 2; 02.01 value 7
Player 2: 01.01. value 3; 02.01 value 2
This is just to make clear, what my goal is. Its not just a graph for each day, but a accumulation of points
But I would have come this far without your help @yosiasz . Cheers!
So are you done or is there anything else missing?
Yes one feature is missing.
I want Grafana to show the scores for week one for each player.
For week 2 I want the accumulated sums (week 1+2) as the score.
For week 3 I want week1+week2+week3 as the score and so on.
See my Post 1 The accumulate scores where you can see, how all the scores add up over the time
So that is not a grafana issue. it is a Mariadb query issue. you need to figure out how to group by week.
1 Like
