Sql Query Solution

Question

I have a query as follows.

select strftime('%Y-%m',A.traDate) as Month,sum(A.TraAmt) as Total,C.GroupType from 
TransactionTbl A left join TransactionCategory B on A.CategoryID = B.CategoryID left join 
CategoryGroup C on B.CatGRoupID=C.CatGRoupID where A.UserID=1 and A.ProfileID=1 and 
date(A.TraDate) between date('2009-12-01') and date('2010-11-01') group by C.GroupType,  
strftime('%m',A.traDate) order by Month

Above query gives result as follows,

Month Total C.GroupType 
----- ----- ----------- 
2009-12 4100 Expense 
2009-12 8000 Income 
2010-01 200     Expense 
2010-01    2000    Income
2010-02    3500    Expense
2010-02    7500    Income

Oke, & I want solution like this.

    Month Income Expense 
    ----- ----- ----------- 
    2009-12 8000    4100
    2010-01 8000    200
    2010-02 7500    3500

I am trying hard to get output like this, but I could find any kind of solution.

Would some one help me please?

I am an iPhone developer & I know a little about sql queries.

Thanks in advance for sharing your knowledge.

Sagar

Answer 1

This is one way of achieving what you want:

SELECT
    T1.Month,
    SUM(CASE T1.GROUPTYPE WHEN 'Income' THEN T1.TOTAL ELSE 0 END) AS Income,
    SUM(CASE T1.GROUPTYPE WHEN 'Expense' THEN T1.TOTAL ELSE 0 END) AS Expense
FROM (/* Your query here */) AS T1
GROUP BY T1.Month