SQL: Join multiple tables and get a grouped sum
- by Scienceprodigy
I have a database with 3 tables that have related data. One table has transactions, and the other two relate to transaction categories. Basically it's financial data, so each transaction has a category (i.e. "gasoline" for a gas purchase transaction). A short version of my Transactions table looks like this-
Transactions Table:
________________________________
| ID | Type | Amount | Category |
---------------------------------
I also have two more tables relating a category to a categories parent. So basically, every Category entry in the Transactions Table belongs to a parent category (i.e. "gasoline" would belong to say "Automotive Expenses"). For categories, and their parent, I have two tables -
Category Children:
____________________________________________
| ID | Parent Category ID | Child Category |
--------------------------------------------
Category Parent:
________________________
| ID | Parent Category |
------------------------
What I'm trying to do is query the database and have it return a total spending by parent category. To get "spending" the Type of transactions must be "Debit".
I tried the following statement:
SELECT category_parents.parent_category,
SUM(amount) AS totals
FROM (transactions
INNER JOIN category_children
ON transactions.category = 'category_children.child_category')
INNER JOIN category_parents
ON category_children.parent_category_id = category_parents._id
WHERE trans_type = 'Debit'
GROUP BY parent_category
ORDER BY totals DESC
but it gives me the following exception:
12-31 13:51:21.515: ERROR/Exception on query(4403): android.database.sqlite.SQLiteException: no such column: category_children.parent_category_id: , while compiling: SELECT category_parents.parent_category, SUM(amount) AS totals FROM (transactions INNER JOIN category_children ON transactions.category='category_children.child_category') INNER JOIN category_parents ON category_children.parent_category_id=category_parents._id where trans_type='Debit' group by parent_category order by totals desc
Any help is appreciated.
(EXTRA CREDIT: I also need to make another statement to do spending by child category, given the parent category)
