From: | Jerry LeVan <jerry(dot)levan(at)eku(dot)edu> |
---|---|
To: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com> |
Cc: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Types and SRF's |
Date: | 2004-09-01 12:24:44 |
Message-ID: | E7DFF7FD-FC11-11D8-BE09-000393779D9C@eku.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-09-01 12:26:37 | Re: Join efficiency |
Previous Message | Russ Brown | 2004-09-01 12:24:41 |