Generate Monthly Aggregated Values in SQlite-CodePudding

I am new in SQLite and i have been working on SQL Query for quite some time.

Lets say we have database table say tbl_expense with the following table structure.

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
)

Assume we have the following data stored in the table.

Expected Output

Assuming we have expense_date as timestamp, How can i write a SQL query in such a way that i can produce the output like the one below.

Please help me on this issue.

CodePudding user response：

You need a LEFT join of a cte that returns all the month abbreviations to the table and conditional aggregation:

WITH cte(month, month_name) AS (VALUES
  ('01', 'JAN'), ('02', 'FEB'), ('03', 'MAR'), ('04', 'APR'), ('05', 'MAY'), ('06', 'JUN'), 
  ('07', 'JUL'), ('08', 'AUG'), ('09', 'SEP'), ('10', 'OCT'), ('11', 'NOV'), ('12', 'DEC')
)
SELECT c.month_name,
       TOTAL(CASE WHEN expense_type = 'Income' THEN expense_amt END) Income,
       TOTAL(CASE WHEN expense_type = 'expense' THEN expense_amt END) Expense
FROM cte c LEFT JOIN tbl_expense e
ON strftime('%m', e.expense_date, 'unixepoch') = c.month
AND strftime('%Y', e.expense_date, 'unixepoch') = strftime('%Y', CURRENT_DATE)
GROUP BY c.month_name
ORDER BY c.month;

The condition:

strftime('%Y', e.expense_date, 'unixepoch') = strftime('%Y', CURRENT_DATE)

is used to return results only for the current year.
You can change it to get results for any year that you want, like:

strftime('%Y', e.expense_date, 'unixepoch') = '2020'

See the demo.