Can I combine my two SQLite SELECT statements into one?

database, sql, sqlite

Solution

Try something like this

select substr(date, 0,7) "Month",
       total(case when a > 0 then a else 0 end) "Income",
       total(case when a < 0 then a else 0 end) "Expenses"
from posts
group by month

Problem

I have a SQLite table called `posts`. An example is shown below. I would like to calculate the monthly income and expenses. ``` accId date text amount balance ---------- ---------- ------------------------ ---------- ---------- 1 2008-03-25 Ex1 -64.9 3747.56 1 2008-03-25 Shop2 -91.85 3655.71 1 2008-03-26 Benny's -100.0 3555.71 ``` For the income I have this query: ``` SELECT SUBSTR(date, 0,7) "month", total(amount) "income" FROM posts WHERE amount > 0 GROUP BY month ORDER BY date; ``` It works fine: ``` month income ---------- ---------- 2007-05 4877.0 2007-06 8750.5 2007-07 8471.0 2007-08 5503.0 ``` Now I need the expenses and I could of cause just repeat the first statement with the condition `amount < 0`, but I am wondering if there is an elegant way to get both income and expenses in one query?

Original source