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 |