help with a mySQL query

[ugasucks]

I have a table that has a column for amount_type and a column for amount per account number. There are 3 amount_type's: current, thirty, sixty. I would like to do something like the following:

select acct, sum(amount), sum(amount), sum(amount)
from ct_table
where amount_type=0,amount_type=1, amount_type=2

and the result return an array such that
acct|current_total|thirty_total|sixty_total.

how do I do this? a friend told me that i would have to use unions, but i could not find that functionality in mySQL. i've tried temporary tables and joins, but can't get it! please help.

[nnichols]

I haven't tested this query but it should do the job.

SELECT acct,
      SUM(IF(amount_type=0,amount,0)) AS current_total,
      SUM(IF(amount_type=1,amount,0)) AS thirty_total,
      SUM(IF(amount_type=2,amount,0)) AS sixty_total 
from ct_table
GROUP BY acct;

Hope this helps 😉