By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,729 Members | 1,122 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,729 IT Pros & Developers. It's quick & easy.

Folding subtotals into query?

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.