Thread: Folding subtotals into query?
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 Into the of the first select. Thanks, Jerry
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
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 >
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
>>
>
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 >>> >> >