[SQL] Accumulated sums in SQL query
Hi, Which is the simplest way to create an SQL query to get accumulated sums of records like this (from the table containing the numbers): numbersums --- 1 1 2 3 3 6 4 10 Thanks in advance Regards, Joseph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Accumulated sums in SQL query
> Which is the simplest way to create an SQL query to get accumulated sums of > records like this (from the table containing the numbers): > > numbersums > --- > 1 1 > 2 3 > 3 6 > 4 10 > SELECT number, SUM(your_sum_column) FROM your_table GROUP BY number ; Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Accumulated sums in SQL query
Thanks, but it seems not to be the solution to me, the example was not good enough. The table for example represents a bank account, records are debits and credits, and I need the balance in the new query: D&C balance --- 11 34 -2 2 57 -3 4 3 7 and so on regards Joseph - Original Message - From: "Christoph Haller" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, October 28, 2002 9:20 AM Subject: Re: [SQL] Accumulated sums in SQL query > > Which is the simplest way to create an SQL query to get accumulated > sums of > > records like this (from the table containing the numbers): > > > > numbersums > > --- > > 1 1 > > 2 3 > > 3 6 > > 4 10 > > > SELECT number, SUM(your_sum_column) FROM your_table > GROUP BY number ; > > Regards, Christoph > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Accumulated sums in SQL query
>
> OK I have a table named bank_account_movements containing two columns
=
> date and amount:
>
> date amount (in USD)
> -
> 2002-10-01 20
> 2002-10-02 30
> 2002-10-03 -15
> 2002-10-04 -5
> 2002-10-05 -3
> 2002-10-0610
>
> my goal is to create a view from it adding an extra column named =
> balance!
>
> date amount (in USD) balance
> ---
> 2002-10-01 20 20
> 2002-10-02 30 50
> 2002-10-03 -1535
> 2002-10-04 -5 30
> 2002-10-05 -3 27
> 2002-10-0610 17
>
> The balance is 0+20=20, 0+20+30=50, 0+20+30-15=35 and so on...
> how would you write the SQL query?
>
My first approach is write a small plpgsql function
(based on the table definition below) like
CREATE TABLE amountlist (date TIMESTAMP,amount INTEGER);
INSERT INTO amountlist VALUES ('2002-10-01 00:00:00', 20 ) ;
INSERT INTO amountlist VALUES ('2002-10-02 00:00:00', 30 ) ;
INSERT INTO amountlist VALUES ('2002-10-03 00:00:00',-15 ) ;
INSERT INTO amountlist VALUES ('2002-10-04 00:00:00', -5 ) ;
INSERT INTO amountlist VALUES ('2002-10-05 00:00:00', -3 ) ;
INSERT INTO amountlist VALUES ('2002-10-06 00:00:00', 10 ) ;
CREATE FUNCTION calc_balance(TIMESTAMP) RETURNS INTEGER AS '
DECLARE balance INTEGER;
BEGIN
SELECT INTO balance SUM(amount) FROM amountlist WHERE date <= $1 ;
RETURN balance;
END;
' LANGUAGE 'plpgsql' ;
SELECT date,amount,calc_balance(date) FROM amountlist;
date | amount | calc_balance
++--
2002-10-01 00:00:00+02 | 20 | 20
2002-10-02 00:00:00+02 | 30 | 50
2002-10-03 00:00:00+02 |-15 | 35
2002-10-04 00:00:00+02 | -5 | 30
2002-10-05 00:00:00+02 | -3 | 27
2002-10-06 00:00:00+02 | 10 | 37
(6 rows)
Looks like what you are looking for, except the last value which
appears to be a typo.
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] VACUUM and locking
I am trying to automate VACUUMing several databases and am confused on how client connections to the database might affect the VACUUM. I have looked at the documentation on locking and VACUUM and have found conflicting reports. The 7.2.1 Documentation http://www.postgresql.org/idocs/index.php?locking-tables.html states that VACUUM (without FULL) acquires a ShareUpdateExclusiveLock and VACUUM with FULL acquires an AcessExclusiveLock. However, the 7.2.1 Appendix A. Release Notes state "Vacuuming no longer locks tables, thus allowing normal user access during the vacuum. A new VACUUM FULL command does old-style vacuum by locking the table and shrinking the on-disk copy of the table." Additionally, the "Transaction Processing in Postgres" pdf http://developer.postgresql.org/pdf/transactions.pdf only shows AcessExclusiveLock acquird by VACUUM (full? - not stated) (p. 18). So which, if any, locks does VACUUM acquire? How do client connections to the database affect the VACUUM? What is the best way to automate VACUUM? Thanks for your help, Denise ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] CoreReader
Does anybody know of CoreReader being run against PostGresql? If so, I would appreciate knowing the results. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Foreign character struggles
On Fri, 2002-10-25 at 15:33, Tim Perdue wrote: > I compiled postgres with --enable-multibyte and --enable-recode, and it > doesn't appear to help with my problem. createdb my_db_name -E LATIN1 Worked just fine for me but the client wanted to be able to search with accents so I turned the to_ascii stuff off. See www.3continents.com/base_de_donnees.htm and search for "Amnésie" if you want the english search to work you search for "Amnesia"... The client wants the user to check spelling... Before it worked just the way you wanted _but_ I am using a JDBC request via JSP. Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] subscrip out of range
i am getting this error when iimport data using the pgaccess client from a txt file. I will import some records then crash out. Any IDeas? has it got anything to do with the primary key on the destination table and if so how do you temporaryily disable it Thanks for any hellp PEter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] multiple databases
I need to create a recordset in vb which combines a view/table from 2 different databases. Is this possible? in mssql you could go dbo.databasename.tablename. Can it be done in vb code? Thanks for any help Peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Hairy question - transpose columns
Hi Andres, For mine, I would read the data in using any langauage that I was familiar with and parse it into an array or some other form in the required format then create the "copy" statement for postgresql, connect and apply. Regards Mark ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] multiple databases
On Sat, Oct 26, 2002 at 03:15:18PM +1000, peter wrote: > I need to create a recordset in vb which combines a view/table from 2 > different databases. Is this possible? in mssql you could go > dbo.databasename.tablename. Can it be done in vb code? There's an extension to PostgreSQL, called dblink, that allows it. Look at the contrib tree on the PostgreSQL archives. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "Data! I thought you were dead!" "No, Sir. I rebooted." ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] CoreReader
On Fri, Oct 25, 2002 at 10:32:00PM -0500, John Ragan wrote: > > Does anybody know of CoreReader being run against > PostGresql? > > If so, I would appreciate knowing the results. What's CoreReader? -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Accumulated sums in SQL query
Thank you Christoph this logic helped me a lot.
Regards Joseph
- Original Message -
From: "Christoph Haller" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 28, 2002 1:27 PM
Subject: Re: [SQL] Accumulated sums in SQL query
> >
> > OK I have a table named bank_account_movements containing two columns
> =
> > date and amount:
> >
> > date amount (in USD)
> > -
> > 2002-10-01 20
> > 2002-10-02 30
> > 2002-10-03 -15
> > 2002-10-04 -5
> > 2002-10-05 -3
> > 2002-10-0610
> >
> > my goal is to create a view from it adding an extra column named =
> > balance!
> >
> > date amount (in USD) balance
> > ---
> > 2002-10-01 20 20
> > 2002-10-02 30 50
> > 2002-10-03 -1535
> > 2002-10-04 -5 30
> > 2002-10-05 -3 27
> > 2002-10-0610 17
> >
> > The balance is 0+20=20, 0+20+30=50, 0+20+30-15=35 and so on...
> > how would you write the SQL query?
> >
>
> My first approach is write a small plpgsql function
> (based on the table definition below) like
>
> CREATE TABLE amountlist (date TIMESTAMP,amount INTEGER);
> INSERT INTO amountlist VALUES ('2002-10-01 00:00:00', 20 ) ;
> INSERT INTO amountlist VALUES ('2002-10-02 00:00:00', 30 ) ;
> INSERT INTO amountlist VALUES ('2002-10-03 00:00:00',-15 ) ;
> INSERT INTO amountlist VALUES ('2002-10-04 00:00:00', -5 ) ;
> INSERT INTO amountlist VALUES ('2002-10-05 00:00:00', -3 ) ;
> INSERT INTO amountlist VALUES ('2002-10-06 00:00:00', 10 ) ;
> CREATE FUNCTION calc_balance(TIMESTAMP) RETURNS INTEGER AS '
> DECLARE balance INTEGER;
> BEGIN
> SELECT INTO balance SUM(amount) FROM amountlist WHERE date <= $1 ;
> RETURN balance;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> SELECT date,amount,calc_balance(date) FROM amountlist;
> date | amount | calc_balance
> ++--
> 2002-10-01 00:00:00+02 | 20 | 20
> 2002-10-02 00:00:00+02 | 30 | 50
> 2002-10-03 00:00:00+02 |-15 | 35
> 2002-10-04 00:00:00+02 | -5 | 30
> 2002-10-05 00:00:00+02 | -3 | 27
> 2002-10-06 00:00:00+02 | 10 | 37
> (6 rows)
>
> Looks like what you are looking for, except the last value which
> appears to be a typo.
>
> Regards, Christoph
>
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Accumulated sums in SQL query
I think,that is not good solution. What will happen when some records
have the same date? I suggest to operate on rowid or something like
thaţ (maybe some serial?) and everything will be good.
But, when each row has different date, its good :)
regards.
marek
On Mon, Oct 28, 2002 at 03:55:46PM +0100, Kabai J?zsef (I don't know why;) wrote:
: Thank you Christoph this logic helped me a lot.
: Regards Joseph
: - Original Message -
: From: "Christoph Haller" <[EMAIL PROTECTED]>
: To: <[EMAIL PROTECTED]>
: Cc: <[EMAIL PROTECTED]>
: Sent: Monday, October 28, 2002 1:27 PM
: Subject: Re: [SQL] Accumulated sums in SQL query
:
:
: > >
: > > OK I have a table named bank_account_movements containing two columns
: > =
: > > date and amount:
: > >
: > > date amount (in USD)
: > > -
: > > 2002-10-01 20
: > > 2002-10-02 30
: > > 2002-10-03 -15
: > > 2002-10-04 -5
: > > 2002-10-05 -3
: > > 2002-10-0610
: > >
: > > my goal is to create a view from it adding an extra column named =
: > > balance!
: > >
: > > date amount (in USD) balance
: > > ---
: > > 2002-10-01 20 20
: > > 2002-10-02 30 50
: > > 2002-10-03 -1535
: > > 2002-10-04 -5 30
: > > 2002-10-05 -3 27
: > > 2002-10-0610 17
: > >
: > > The balance is 0+20=20, 0+20+30=50, 0+20+30-15=35 and so on...
: > > how would you write the SQL query?
: > >
: >
: > My first approach is write a small plpgsql function
: > (based on the table definition below) like
: >
: > CREATE TABLE amountlist (date TIMESTAMP,amount INTEGER);
: > INSERT INTO amountlist VALUES ('2002-10-01 00:00:00', 20 ) ;
: > INSERT INTO amountlist VALUES ('2002-10-02 00:00:00', 30 ) ;
: > INSERT INTO amountlist VALUES ('2002-10-03 00:00:00',-15 ) ;
: > INSERT INTO amountlist VALUES ('2002-10-04 00:00:00', -5 ) ;
: > INSERT INTO amountlist VALUES ('2002-10-05 00:00:00', -3 ) ;
: > INSERT INTO amountlist VALUES ('2002-10-06 00:00:00', 10 ) ;
: > CREATE FUNCTION calc_balance(TIMESTAMP) RETURNS INTEGER AS '
: > DECLARE balance INTEGER;
: > BEGIN
: > SELECT INTO balance SUM(amount) FROM amountlist WHERE date <= $1 ;
: > RETURN balance;
: > END;
: > ' LANGUAGE 'plpgsql' ;
: >
: > SELECT date,amount,calc_balance(date) FROM amountlist;
: > date | amount | calc_balance
: > ++--
: > 2002-10-01 00:00:00+02 | 20 | 20
: > 2002-10-02 00:00:00+02 | 30 | 50
: > 2002-10-03 00:00:00+02 |-15 | 35
: > 2002-10-04 00:00:00+02 | -5 | 30
: > 2002-10-05 00:00:00+02 | -3 | 27
: > 2002-10-06 00:00:00+02 | 10 | 37
: > (6 rows)
: >
: > Looks like what you are looking for, except the last value which
: > appears to be a typo.
: >
: > Regards, Christoph
: >
: >
: >
: > ---(end of broadcast)---
: > TIP 3: if posting/reading through Usenet, please send an appropriate
: > subscribe-nomail command to [EMAIL PROTECTED] so that your
: > message can get through to the mailing list cleanly
: >
:
:
:
: ---(end of broadcast)---
: TIP 2: you can get off all lists at once with the unregister command
: (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Marek Bartnikowski http://easy.eu.org
* It is more complicated than you think *
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] VACUUM and locking
"Denise Bossarte" <[EMAIL PROTECTED]> writes: > I have looked at the documentation on locking and VACUUM and have found > conflicting reports. > http://www.postgresql.org/idocs/index.php?locking-tables.html states that > VACUUM (without FULL) acquires a ShareUpdateExclusiveLock and VACUUM with > FULL acquires an AcessExclusiveLock. This is correct. > However, the 7.2.1 Appendix A. Release Notes state "Vacuuming no longer > locks tables, thus allowing normal user access during the vacuum. A new > VACUUM FULL command does old-style vacuum by locking the table and shrinking > the on-disk copy of the table. This is an oversimplification, as it says "lock" where it means AccessExclusiveLock. > Additionally, the "Transaction Processing > in Postgres" pdf http://developer.postgresql.org/pdf/transactions.pdf only > shows AcessExclusiveLock acquird by VACUUM (full? - not stated) (p. 18). This document is pre-7.2. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] psql history
Hi everibody, i have installed Postgres 7.2.2 from a tarball, but using psql i can not have the history of the last command. When i used Postgres from rpm this useful element worked very well! Why that? Best Regards Massimo Arnaudo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] subscrip out of range
"subscript out of range" is an error that means you are trying to access part of an array that doesnt exist. It probably means that your data doesnt have all the fields for all the records. Check to see if your data is truncated or if there is an unusual (usually shortened) number of fields in a given record. HTH Chad - Original Message - From: "peter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, October 26, 2002 1:25 AM Subject: [SQL] subscrip out of range > i am getting this error when iimport data using the pgaccess client from a > txt file. I will import some records then crash out. Any IDeas? > > has it got anything to do with the primary key on the destination table and > if so how do you temporaryily disable it > > Thanks for any hellp > > PEter > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] BOOLEAN question
Folks, Odd question: I have a query that returns a set of none to several rows. One column in these rows is BOOLEAN. I want the query to return: 1) TRUE if *any* of the BOOLEAN values is TRUE; 2) FALSE if *all* of the BOOLEAN values are FALSE; 3) FALSE or NULL if no rows are returned. I thought that I could do this through a MAX(boolean), but as it turns out, MAX(boolean) has not been defined in the standard 7.2.3. distro. I could define MAX(boolean), but I'm wondering if I'm missing something obvious. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] BOOLEAN question
On Mon, 28 Oct 2002, Josh Berkus wrote: > Odd question: I have a query that returns a set of none to several rows. One > column in these rows is BOOLEAN. I want the query to return: > 1) TRUE if *any* of the BOOLEAN values is TRUE; > 2) FALSE if *all* of the BOOLEAN values are FALSE; > 3) FALSE or NULL if no rows are returned. > > I thought that I could do this through a MAX(boolean), but as it turns out, > MAX(boolean) has not been defined in the standard 7.2.3. distro. I could > define MAX(boolean), but I'm wondering if I'm missing something obvious. Well, you might be better off making a max(bool), but a not super-efficient version might be: max(case when col then 1 else 0 end)=1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] BOOLEAN question
Stephan, > Well, you might be better off making a max(bool), but a not > super-efficient version might be: > max(case when col then 1 else 0 end)=1 Good, I'm not just brain-dead. I did figure out another way to do it, but if I wrote a MAX(bool), what are the chances it would get added to the core? I don't see any good reason not to have one. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] BOOLEAN question
On Mon, 28 Oct 2002, Josh Berkus wrote: > Stephan, > > > Well, you might be better off making a max(bool), but a not > > super-efficient version might be: > > max(case when col then 1 else 0 end)=1 > > Good, I'm not just brain-dead. I did figure out another way to do it, but if > I wrote a MAX(bool), what are the chances it would get added to the core? I > don't see any good reason not to have one. Don't know really. I guess if we're saying that true>false it might make sense, although the notion of max(bool) seems odd to me, especially since it only easily handles an ANY case. Speaking of which, a more expensive but possibly nicer looking way would be something like true=ANY(select col from ) (which of course also generalizes into =ALL fairly easily) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
