Re: Folding subtotals into query?

On Apr 18, 2004, at 4:07 PM, Jerry LeVan wrote:

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

hmm... Can do this via a left join too. Much faster:

SELECT checks.*, x.sum
FROM checks
LEFT JOIN (SELECT category, sum(amount) AS sum FROM checks GROUP BY
category) AS x ON x.category = checks.category
ORDER BY category, sum

eric

>
> --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
>>
>