Re: Folding subtotals into query?

That does the job, for 3200 checks it does chug for a while, too bad
it can't remember the intermediate results :)

--Jerry

On Apr 18, 2004, at 3:10 PM, Eric Ridge wrote:

> On Apr 18, 2004, at 2:41 PM, Jerry LeVan wrote:
>
>> Is is possible, via some clever sql coding ( perhaps with PL/pgsql)
>> to get subtotals to appear in a selection, ie
>>
>> If I have a query: select * from checks order by category
>> I would like the have the subtotals appear (possibly in
>> an unused column for each "category" when the category
>> "breaks".
>>
>> Basically I would like to meld the query:
>> select category, sum(amount) from checks group by category order by
>> category
>
> I think you want to do something like this:
>
> SELECT *, (SELECT sum(amount) FROM checks AS x WHERE x.category =
> checks.category GROUP BY x.category) AS total
> FROM checks
> ORDER BY category;
>
> This will give you a column named "total" for every row in checks.
> The value will be the sum(amount) for the corresponding category.
> You'll likely want an index on checks.category to get any level of
> tolerable performance out of the query.
>
> eric
>