SQLite: Get Output From Two Tables Using Common Reference ID

February 4, 2022

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;