I am new in SQLite and i have been working on an issue for quite a long time.
Lets say we have 2 database table say tbl_expense and tbl_category. Please find below the following table structure.
tbl_category
CREATE TABLE IF NOT EXISTS tbl_category(
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category_name VARCHAR(20) DEFAULT NULL,
category_desc VARCHAR(500) DEFAULT NULL,
category_icon VARCHAR(100) DEFAULT NULL,
category_created timestamp default CURRENT_TIMESTAMP
)
tbl_expense
CREATE TABLE IF NOT EXISTS tbl_expense(
expense_id INTEGER PRIMARY KEY AUTOINCREMENT,
expense_name VARCHAR(20) DEFAULT NULL,
expense_desc VARCHAR(500) DEFAULT NULL,
expense_type VARCHAR(20) DEFAULT NULL,
expense_amt DECIMAL(6.3) DEFAULT NULL,
expense_date TIMESTAMP DEFAULT NULL,
expense_category INTEGER DEFAULT NULL,
expense_created_date timestamp DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (expense_category) REFERENCES tbl_category(category_id)
ON DELETE SET NULL
)
Assume we have data in the tables like this below.
Expected Output:
Assure we have category_id and expense_category as common fields. How can i create an SQL Query where i can list all categories and sum of their expense amount as follows.
Please help me on this issue.
>Solution :
You need an INNER
join of the tables and aggregation:
SELECT c.category_name Category,
SUM(e.expense_amt) Amount
FROM tbl_category c INNER JOIN tbl_expense e
ON e.expense_category = c.category_id
GROUP BY c.category_id;
If you want all categories from the table tbl_category
, even those that are not present in tbl_expense
, use a LEFT
join and TOTAL()
aggregate function:
SELECT c.category_name Category,
TOTAL(e.expense_amt) Amount
FROM tbl_category c LEFT JOIN tbl_expense e
ON e.expense_category = c.category_id
GROUP BY c.category_id;