Help with SQLITE DB

[Tuxedo]

I have a two part question. I've created a DB via SQLITE containing personal financial transactions consisting of transaction date, amount, description, and an assigned category (e.g. food, clothing, taxes, etc.). I have 43 categories in total and the data spans multiple years. My goal is to be able to sum the amounts by category for each year and do a year by year comparision.

Q1: right now I have all of the data in a single table. Considering my goal, is there a more appropriate design (e.g. each year in a different table)?

Q2: is there a clever SQL query I can use to sum the amounts for each category/year combination? I envision perhaps a Pandas dataframe that I'd export to an Excel file. I suppose I could do a separate SQL query for each category and year via a nested loop but I'm guessing there is a more efficient/streamlined way to do it.

I'd appreciate your comments.

[buran]

Q1: right now I have all of the data in a single table. Considering my goal, is there a more appropriate design (e.g. each year in a different table)?

No, your design looks good. Table per year would be plainly wrong

Q2: is there a clever SQL query I can use to sum the amounts for each category/year combination? I envision perhaps a Pandas dataframe that I'd export to an Excel file. I suppose I could do a separate SQL query for each category and year via a nested loop but I'm guessing there is a more efficient/streamlined way to do it.

Not tested, but something like

Output:
SELECT SUM(amount) from table_name GROUP BY strftime('%Y',date_column), category

or GOUP BY category, strftime('%Y',date_column) - depending on which column you want to group by first.

Of course, the same can be done in pandas, with pivot_table() or groupby()


EDIT: see correct example here: https://python-forum.io/Thread-Help-with...#pid137691

[Tuxedo]

Hmmm, I'll try both. Thanks!

[buran]

I edited the sql a bit to get just the year

[buran]

import sqlite3

conn = sqlite3.connect('test1.sqlite3')
sql="""BEGIN TRANSACTION;
CREATE TABLE `transactions` (
	`tdate`	TEXT,
	`category`	TEXT,
	`amount`	INTEGER
);
INSERT INTO `transactions` (tdate,category,amount) VALUES ('2020-01-01','food',1),
 ('2020-02-01','food',3),
 ('2021-01-05','food',5),
 ('2021-01-10','fuel`',10);
COMMIT;"""
conn.executescript(sql)

sql = 'SELECT * from transactions'
result =  conn.execute(sql)
for row in result:
    print(row)

sql = """SELECT strftime('%Y',  tdate) as Year, 
                category, 
                SUM(amount) As Amount 
         FROM transactions
         GROUP BY Year,  category"""

result =  conn.execute(sql)
for row in result:
    print(row)

Output:
('2020-01-01', 'food', 1)
('2020-02-01', 'food', 3)
('2021-01-05', 'food', 5)
('2021-01-10', 'fuel`', 10)
('2020', 'food', 4)
('2021', 'food', 5)
('2021', 'fuel`', 10)

[Tuxedo]

Wow, I have so much to learn. Thanks!