Q:

birt merge columns

I'm using it to generate user reports. I need to create one that shows number and sum of topups and the sum and number of withdrawals.
I created two data sets:
select WAL_TRANSACTION.PARTY_ID, sum(WAL_TRANSACTION.AMOUNT) amountdep, count(WAL_TRANSACTION.AMOUNT) countdep
from WAL_TRANSACTION
where WAL_TRANSACTION.TRANSACTION_TYPE=1
and WAL_TRANSACTION.STATUS='completed'
group by WAL_TRANSACTION.PARTY_ID

and 
select WAL_TRANSACTION.PARTY_ID, sum(WAL_TRANSACTION.AMOUNT) amountwi, count(WAL_TRANSACTION.AMOUNT) countwi
from WAL_TRANSACTION
where WAL_TRANSACTION.TRANSACTION_TYPE=2
and WAL_TRANSACTION.STATUS='completed'
group by WAL_TRANSACTION.PARTY_ID

And then created a BIRT joint data set to combine both (full outer join)
The problem is that in the result, you end up with 2 PARTY_ID columns, is there a way to merge them into one column?
sample current result

A:

Just use conditional aggregation:
select t.PARTY_ID,
       sum(case when t.TRANSACTION_TYPE = 1 then t.AMOUNT end) as amountdep_1,
       sum(case when t.TRANSACTION_TYPE = 1 then 1 else 0 end) as countdep_1,
       sum(case when t.TRANSACTION_TYPE = 2 then t.AMOUNT end) as amountdep_2,
       sum(case when t.TRANSACTION_TYPE = 2 then 1 else 0 end) as countdep_2
from WAL_TRANSACTION t
where t.TRANSACTION_TYPE in (1, 2) and
      t.STATUS = 'completed'
group by t.PARTY_ID;

You don't need subqueries for this.

