SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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
)
✓
INSERT INTO tbl_expense VALUES
  (1, 'food expense', 'food expense', 'expense', 2000, 1644128924), 
  (2, 'income', 'money added', 'Income', 3000, 1644128924), 
  (3, 'fruits expense', 'fruits expense', 'expense', 300, 1641450524), 
  (4, 'food expense', 'food expense', 'expense', 50, 1644128924);  
✓
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;


month_name
Income
Expense


JAN
0
300

FEB
3000
2050

MAR
0
0

APR
0
0

MAY
0
0

JUN
0
0

JUL
0
0

AUG
0
0

SEP
0
0

OCT
0
0

NOV
0
0

DEC
0
0

month_name	Income	Expense
JAN	0	300
FEB	3000	2050
MAR	0	0
APR	0	0
MAY	0	0
JUN	0	0
JUL	0	0
AUG	0	0
SEP	0	0
OCT	0	0
NOV	0	0
DEC	0	0