Re: Folding subtotals into query?

Wow, much faster

Jerry

On Apr 18, 2004, at 4:20 PM, Eric Ridge wrote:

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