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*******@postg resql.org 4 3499
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*******@postg resql.org so that your
message can get through to the mailing list cleanly
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Nuff Said |
last post by:
Question:
Is there a way to unfold a Python class *without* unfolding
all its methods etc. so that you can get a quick overview
of the class?
Details:
I normally open a file either with 'fold.on.open=1' set in
the user's options file resp. I use 'toggle all folds' from
|
by: Scott |
last post by:
How can you show SUBTOTALS in the same column as the data, NOT in a
separate column?
I need to SUBTOTAL in the same column as one of the the data columns,
like the subtotals 700 and 800 under Field 3 below (I really have 21
columns). Also I must show one blank row after each resulting group:
Field1 Field2 Field3 Field4
entry1 data2.1 100 data4.1
entry2 data2.2 400 data4.2
|
by: John Baker |
last post by:
Hi:
I have a situation where I have a series of time records (one or more per day) for a
number of projects. Each record has an identification for the activity conducted during
the period recorded. For any project, the activity may be one of many things, and the
activities may vary by project number.
I need to run a report and end up with a list of all the times reported, and totals by the
project number and the activity, as well as...
|
by: Richard Grene |
last post by:
I am creating an Excel spreadsheet using a query function in my vb code. This works fine. I can format the columns but dont know how to create subtotals and a grand total.
Thanks,
Richard
|
by: Greg |
last post by:
I have a database that tracks the vacation time of each employee of the
company that I work for. Everything works great and totals all of the
vacation time that has been used. Could someone give me a little
guidance on how I could get a subtotal for each employee and then total
all of the subtotals? I would like to list employee 1's time and total
it, then employee 2's time and total it and so on. Then total all of
the subtotals. Not sure...
| |
by: John Salerno |
last post by:
Specifically, I'm using UltraEdit and perhaps there's no way perfect way
to implement code folding with it, given how it uses its syntax
highlighting file to do so (i.e., you have to specify an "Opening" and
"Closing" character in which to enfold code, such as braces).
But my question is more general: is it possible to implement code
folding with Python given that it has no real block delimiters? Or is
this still a matter of which...
|
by: Paul Ilacqua |
last post by:
I'm using VB .NET and I'm trying to "roll my own" report subtotals. I'm
building a string from a datareader top pass to a reporting class and I want
to break and subtotal QTY at each change in shipper and I have struggled
with the code to do so for days and just can't seem to get it. I can get it
to insert a draw a line at a shipper change, but I can't get the subtotals.
The actual Code is down below data sample. I appreciate any advice I...
|
by: =?Utf-8?B?SHV0dHk=?= |
last post by:
I have searched the net but have been unable to find a solution to
subtotaling in a gridview. I have a gridview that is bounded to sqldatasource
where I would like to subtotal rows throughout the gridview based on column
value "Type". I am using VB.NET. Thanks.
--
Hutty
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |