Hi, Why using autojoin to perform a normal query? Try something like:
SELECT accno,description,SUM(amount) FROM acc_trans ,chart WHERE chart_id = chart.id AND accno like '648%' GROUP BY accno,description; I use accno like '648%' because my tax accounts are '648....', you can put there whatever indicate your tax accounts. Luca Scott Taylor wrote: >I have got the following query which gives me all the transactions that have >tax. > >accs=# SELECT f1.trans_id,f1.chart_id,f1.amount,f1.transdate from acc_trans >f1,acc_trans f2 where f1.trans_id >accs-# =f2.trans_id and f2.chart_id=10074 >accs-# and f2.transdate >='2002-07-01' and f2.transdate <='2002-09-30'; > >trans_id | chart_id | amount | transdate >----------+----------+---------+------------ > 10162 | 10004 | -555.19 | 2002-07-03 > 10162 | 10037 | 472.5 | 2002-07-03 > 10162 | 10074 | 82.69 | 2002-07-03 > 10162 | 10004 | 555.19 | 2002-07-25 > 10162 | 10002 | -555.19 | 2002-07-25 > 10164 | 10004 | -616.88 | 2002-07-17 > 10164 | 10037 | 525 | 2002-07-17 > 10164 | 10074 | 91.88 | 2002-07-17 > 10164 | 10004 | 616.88 | 2002-07-31 > 10164 | 10002 | -616.88 | 2002-07-31 > >I want to separate the amounts according to what chart_id they have. > >Do you know how I can put each amount in it's own column like this and >total them? > > >> trans_id | chart_id | amount | amount1 | amount2 | amount3 | amount4 >>----------+----------+---------+------------+------------+----------+------ >>---- 10162 | 10004 | -555.19 >> 10162 | 10037 | 472.5 >> 10162 | 10074 | 82.69 >> 10162 | 10004 | 555.19 >> 10162 | 10002 | > -555.19 >> >>TOTAL -555.19 472.5 82.69 555.19 -555.19 >> >>Cheers >> >>Scott >> >>
