Re: [SQL] How to GROUP results BY month
i think this work select id,count from table group by to_char(date,'MM') --- On Wed, 7/16/08, A. Kretschmer <[EMAIL PROTECTED]> wrote: > From: A. Kretschmer <[EMAIL PROTECTED]> > Subject: Re: [SQL] How to GROUP results BY month > To: pgsql-sql@postgresql.org > Date: Wednesday, July 16, 2008, 5:39 AM > am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark > Roberts folgendes: > > > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer > wrote: > > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte > Oliveiros Cristina folgendes: > > > > Howdy, all, > > > > > > > > I have a problem. > > > > > > > > I have a table which one of the fields is of > type date. > > > > > > > > I need to obtain the totals of the other > fields in a by-month basis > > > > IS there any easy way to do this using the > GROUP BY or any other construct? > > > > > > ... group by extract(month from date) > > > > > > > > > Andreas > > > > It's worth noting that extract(month from > timestamp) returns a month_no, and thus will not be > suitable for grouping queries that span years. > > Right, but that wasn't the question... > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to GROUP results BY month
Still another way to do :-) Thanks, Lennin. Best, Oliveiros - Original Message - From: "Lennin Caro" <[EMAIL PROTECTED]> To: ; "A. Kretschmer" <[EMAIL PROTECTED]> Sent: Friday, July 18, 2008 3:04 PM Subject: Re: [SQL] How to GROUP results BY month i think this work select id,count from table group by to_char(date,'MM') --- On Wed, 7/16/08, A. Kretschmer <[EMAIL PROTECTED]> wrote: From: A. Kretschmer <[EMAIL PROTECTED]> Subject: Re: [SQL] How to GROUP results BY month To: pgsql-sql@postgresql.org Date: Wednesday, July 16, 2008, 5:39 AM am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes: > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: > > > Howdy, all, > > > > > > I have a problem. > > > > > > I have a table which one of the fields is of type date. > > > > > > I need to obtain the totals of the other fields in a by-month basis > > > IS there any easy way to do this using the GROUP BY or any other construct? > > > > ... group by extract(month from date) > > > > > > Andreas > > It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years. Right, but that wasn't the question... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE
On Thu, 2008-07-17 at 12:16 -0400, Alvaro Herrera wrote: > Volkan YAZICI wrote: > > Hi, > > > > What's the difference between below two queue implementations? > > They are two different lock spaces. pg_advisory_lock does not conflict > with regular system locks, whereas LOCK TABLE does. > > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > It also appears that advisory locks are tied to your session, where system locks are tied to your transaction. Also, lock table is a bit more forceful, because it will affect things that don't bother checking advisory locks (such as users, manual scripts, buggy applications, etc). Don't forget that you can use select for update another locking mechanism as well. Well, that's my take on it. -Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE
Mark Roberts wrote: > > On Thu, 2008-07-17 at 12:16 -0400, Alvaro Herrera wrote: > > Volkan YAZICI wrote: > > > Hi, > > > > > > What's the difference between below two queue implementations? > > > > They are two different lock spaces. pg_advisory_lock does not conflict > > with regular system locks, whereas LOCK TABLE does. > > It also appears that advisory locks are tied to your session, where > system locks are tied to your transaction. Now that you mention that, I recall that the other important difference is that you can release pg_advisory_lock anytime you want. With LOCK TABLE it always happens automatically at transaction commit (and there's no UNLOCK TABLE). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
