I'm designing a budgeting website for the final project. One of the features that I want to implement is to be able to track how much money you've spent for each item category.
The SQL command that I'm using is:
SELECT category, sum(amount) FROM transactions GROUP BY category HAVING id=:id
where amount refers to the cost of each expense, category refers to category of expense etc etc. I'm trying to extract two arrays from the query, one containing the names of the categories and the other containing the sum(amounts). My code looks like this:
amounts=db.execute("SELECT category, sum(amount) FROM transactions GROUP BY category HAVING id=:id", id=session["user_id"]) #define two arrays containing the category names and the sum totals for i in amounts: categories = [amounts["category"]] total_amount = [float(amounts["sum(amount)"])] #find the details of previous expenses records = db.execute("SELECT * FROM users WHERE id=:id", id=session["user_id"]) #calculate the total expenses total_expenses = float(records["income"]) - float(records["savings"]) - float(records["balance"]) #calculate the amount as a percentage of the whole for i in range(len(total_amount)): total_amount[i]/=total_expenses #render chart chart = pie_chart(total_amount, categories)
However, the pie chart that comes out only has one value "Clothing" which occupies 100% of the chart. In reality, there should be 3 categories: Clothing, Food, Drinks like below:
category sum(amount) ---------- ----------- Clothing 100 Drinks 5 Food 10