1

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[0]["category"]]
    total_amount = [float(amounts[0]["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[0]["income"]) - float(records[0]["savings"]) - float(records[0]["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
1

This categories = [amounts[0]["category"]] looks suspicious, since categories is always updated with the first element of amounts. Ditto total_amount.

1
  • Got it, thanks! +1
    – neetdays
    Jun 29 '17 at 0:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .