Thank you for the response Pierre,
select category, sum(amount) as sum_amount, extract (month from date) as month
from all_accounts where (extract(year from date)=2003)
group by category,month order by category,month
is certainly much faster than what I am doing but as you pointed out, I want the table to have a column for each month ( and a grand total as the last column).
I have not used arrays and aggregates, I will take a look....
Jerry
On Sep 1, 2004, at 3:03 AM, Pierre-Frédéric Caillaud wrote:
Your query looks suspiciously complicated... Why not process all 12 months in one shot with something like this : - only one subquery - no join - date between can make an index scan on date
select category, sum(amount) as sum_amount, extract (month from date) as month
from all_accounts where (date between beginning of the year and end of the year)
group by category,month order by category,month )
Not what you wanted but probably massively faster.
Or you can do this (in approximate SQL):
create type annual_report_type as ( sums numeric(9,2)[12] );
create type my_type as ( month integer, amount numeric );
CREATE AGGREGATE my_sum
takes one input which is my_type and sums the amount into the month column of annual_report_type
Then :
select category, my_sum( my_type(month,amount) as report, extract (month from date) as month
from all_accounts where (date between beginning of the year and end of the year)
group by category,month order by category,month )
Dunno if this would work, it would be nice I think.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
