how to sum the amount based on conditions ?
Posted on 2007-11-19
I have a view called vw_invoice which has attributes amount, fiscal_key, splr_name, splr_id, adv_flag.
I need to sum all the amounts for current fiscal year, sum all the amounts for all the fiscal periods thus far - by supplier,sum all the amounts
thus far for supplier where adv_flag is true, sum all the amounts for the current fiscal period where adv_flag is true.
eg: the output would be
splr_id , splr_name, address, amt_current_fiscal, sum_total, amt_current_fiscal_adv_flag, sum_total_adv_flag
1 tesname, houston, 100000.00, 20000000.00, 500000.00, 5430000.00
select splr_id, splr_name, sum(amount) as sum_total from vw_invoice
group by splr_id, splr_name --- this would get me current total amount how would I get the rest ?
the fiscal_key would be a parameter.