Feb-18-2021, 07:28 PM
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.
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.