Creating a monthly expense bar chart

Grafana MySQL
,
1

Hi,

I need to create a bar chart from a MySQL database where I have the following columns to track expenses:

ID, YEAR, MONTH, DAY, CURRENCY, TYPE, AMOUNT, GENRE, INFO, ACCOUNT.

I’m trying something like:

SELECT MONTH, SUM(AMOUNT) as “Monthly Expense Total”
FROM myDatabase.myTable
WHERE TYPE = “EXPENSE” AND YEAR = 2022
GROUP BY MONTH

This looks OK in a table visualization but I cannot create a bar-chart.

My aim is to show in the sum of expenses of each month of 2022 in a bar-chart.

Like this:
Axis X = Month
Axis Y = Sum of expenses amount

Is extremely simple, but I couldn’t get it done so far.

Thanks in advance.

2

could you please provide sample data as follows (since we have no access to your server, we need to emulate it)

create table sample_expense(ID int, YEAR int, MONTH int, DAY int,
 CURRENCY money, TYPE int, AMOUNT money,
 GENRE varchar(50), INFO varchar(50), ACCOUNT varchar(50))

insert into sample_expense
values ('2022-03-21 13:00:14','op2250_1_tcy',29) --fill in the rest with bogus but near to real data.
3

Hi yosiasz,

Yes, of course.

Table structure:

CREATE TABLE Transactions (
ID int NOT NULL AUTO_INCREMENT,
YEAR int DEFAULT NULL,
MONTH int DEFAULT NULL,
DAY int DEFAULT NULL,
CURRENCY varchar(45) DEFAULT NULL,
TYPE varchar(45) DEFAULT NULL,
AMOUNT double DEFAULT NULL,
GENRE varchar(45) DEFAULT NULL,
INFO varchar(45) DEFAULT NULL,
PRIMARY KEY (ID),
UNIQUE KEY ID_UNIQUE (ID) )

Some basic examples of what I record in the database:

INSERT INTO database.Transactions(YEAR, MONTH, DAY, CURRENCY, TYPE, AMOUNT, GENRE, INFO) VALUES(2022, 04, 12, ‘USD’, ‘EXPENSE’, 12.5, ‘FOOD’, ‘Starbucks’);

INSERT INTO database.Transactions(YEAR, MONTH, DAY, CURRENCY, TYPE, AMOUNT, GENRE, INFO) VALUES(2022, 03, 31, ‘USD’, ‘INCOME’, 1500, ‘SALARY’, ‘March Paycheck’);

INSERT INTO database.Transactions(YEAR, MONTH, DAY, CURRENCY, TYPE, AMOUNT, GENRE, INFO) VALUES(2022, 02, 10, ‘USD’, ‘EXPENSE’, 120, ‘FURNITURE’, ‘IKEA’);

My aim is to have a bar-chart that shows:

  • February total expense: $ 120
  • March total expense: $ 0 (because the only transaction is “INCOME” TYPE).
  • April total expense: $ 12.5

Thanks in advance for the help provided.

4

I’ve been trying something like:

select MONTH, SUM(AMOUNT)
from database.Transactions
WHERE YEAR = 2022 and TYPE = ‘EXPENSE’
group by MONTH
order by MONTH;

This allows me to create a simple table with the totals of each month (and that’s what I’m looking for, BUT I’m not able to create the bar-chart)

5

Nice. CURRENCY varchar(45) ? That is problematic data type for MONEY type.

Anyways let me try this sample data

6

It’s just to know the currency because sometimes I have movements in EUR for example. Don’t pay so much attention to it, thanks!!!

7

what do you have for X axis on your bar chart?

image
image3175×1127 316 KB

1 Like
8

Correct! That’s exactly what I need but for some reason I don’t see the field “X Axis”…

Any clue?

Screen Shot 2022-04-12 at 12.35.16
Screen Shot 2022-04-12 at 12.35.161568×766 42.2 KB

9

no idea what the issue could be. what version of grafana ?

10

Mine is 8.3.5… I’ll check if there’s any update to install.