4267067 298690 2010-07-05 00:59:15.187
>>>>>> 2010-07-05 01:32:48.300 1726 3212 1428 333
>>>>>> 4270023 298734 2010-07-05 01:59:02.497
>>>>>> 2010-07-05 02:32:48.780
On Thu, Jul 22, 2010 at 11:31:23AM +, Tim Landscheidt wrote:
> Richard Huxton wrote:
>
> >>> What I want to get is the values grouped by "subset", where a subset is a
> >>> set of rows with identical column until the colum changes.
> >>> Is there a way to get
>
> >>> | 2 | B |
> >>> | 4 | C
b.c) = '-1 day'::interval)
)
) th
ON fo.a = th.b
AND fo.b <= th.c
GROUP BY fo.parcela,fo.a,fo.b,fo.c
) tudo
GROUP BY tudo.a,tudo.c,tudo.d
To:
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets
Hi,
having a table similar to
| 1 | B | [2010-07-15 Do] |
|
of the item
>>>> 5. Column: ID R
>>>> 6. Column: ID L
>>>> 7. Column: ID B
>>>> 8. Column: Sum of components
>>>>
>>>> Requirement:
>>>> Sum over all components (from column 8) for each combination of ID R, ID
>&
le b
ON (b.b <> a.b)
AND ((age(a.c,b.c) = '-1 day'::interval)
)
) th
ON fo.a = th.b
AND fo.b <= th.c
GROUP BY fo.parcela,fo.a,fo.b,fo.c
) tudo
GROUP BY tudo.a,tudo.c,tudo.d
To:
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets
Hi,
having a table simi
a while, so I don't know if you are still interested in this
>>> problem or if you, in the meantime, found yourself a solution,
>>> but I've tried this on a local copy of the example you provided and it
>>> seems to work.
>>>
>>> The prob
,fo.a,fo.b,fo.c
) tudo
GROUP BY tudo.a,tudo.c,tudo.d
To:
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets
Hi,
having a table similar to
| 1 | B | [2010-07-15 Do] |
| 1 | B | [2010-07-16 Fr] |
|---+---+-|
| 2 | C | [2010-07-17 Sa] |
| 2 | C | [2010-07-1
OUP BY se.a,se.b,se.c
> ) fo
> LEFT JOIN
> (
> SELECT a.*
> FROM yourTable a
> JOIN yourTable b
> ON (b.b <> a.b)
> AND ((age(a.c,b.c) = '-1 day'::interval)
> )
> ) th
> ON fo.a = th.b
> AND fo.b <= th.c
> GROUP BY fo.parcela,fo.a,fo.b,fo.c
>
l)
)
) th
ON fo.a = th.b
AND fo.b <= th.c
GROUP BY fo.parcela,fo.a,fo.b,fo.c
) tudo
GROUP BY tudo.a,tudo.c,tudo.d
To:
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets
Hi,
having a table similar to
| 1 | B | [2010-07-15 Do] |
| 1 | B | [2010-07-16 Fr] |
|---+---+-
Rainer Stengele wrote:
> yes, the date is always incremented - but anyway the date
> column is not really the point! Actually the first tow
> columns are relevant. I want them gouped together as
> indicated, adding up column 1 in the blocks with identical
> second column, but not adding up over a
gt; The dates always follow that sequential pattern?
>
> Or can be holes on the dates sequence?
>
> Best,
> Oliveiros
>
> - Original Message - From: "Rainer Stengele"
>
> To:
> Sent: Thursday, July 22, 2010 9:09 AM
> Subject: [SQL] grouping s
Howdy, Rainer.
Please advice me,
The dates always follow that sequential pattern?
Or can be holes on the dates sequence?
Best,
Oliveiros
- Original Message -
From: "Rainer Stengele"
To:
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets
Hi,
havi
Richard Huxton wrote:
>>> What I want to get is the values grouped by "subset", where a subset is a
>>> set of rows with identical column until the colum changes.
>>> Is there a way to get
>>> | 2 | B |
>>> | 4 | C |
>>> | 4 | B |
>>> | 3 | D |
>>> by SQL only?
>> I think, the problem is that
On 22/07/10 11:02, A. Kretschmer wrote:
In response to Rainer Stengele :
What I want to get is the values grouped by "subset", where a subset is a set
of rows with identical column until the colum changes.
Is there a way to get
| 2 | B |
| 4 | C |
| 4 | B |
| 3 | D |
by SQL only?
I think, t
In response to Rainer Stengele :
> Hi,
>
> having a table similar to
>
> | 1 | B | [2010-07-15 Do] |
> | 1 | B | [2010-07-16 Fr] |
> |---+---+-|
> | 2 | C | [2010-07-17 Sa] |
> | 2 | C | [2010-07-18 So] |
> |---+---+-|
> | 1 | B | [2010-07-19 Mo] |
> | 1 | B | [201
Hi,
having a table similar to
| 1 | B | [2010-07-15 Do] |
| 1 | B | [2010-07-16 Fr] |
|---+---+-|
| 2 | C | [2010-07-17 Sa] |
| 2 | C | [2010-07-18 So] |
|---+---+-|
| 1 | B | [2010-07-19 Mo] |
| 1 | B | [2010-07-20 Di] |
| 1 | B | [2010-07-21 Mi] |
| 1 | B | [2010
I forgot the list.
-- Forwarded message --
From: Osvaldo Kussama <[EMAIL PROTECTED]>
Date: Mon, 27 Oct 2008 12:28:57 -0200
Subject: Re: [SQL] grouping/clustering query
To: David Garamond <[EMAIL PROTECTED]>
2008/10/24, David Garamond <[EMAIL PROTECTED]>:
Tony, Joe, Steve,
Thanks for the follow-ups. Yes, the problem is related to double-entry
accounting, where one needs to balance total debit and credit
(payments and invoices) in each journal/transaction.
Due to time constraint, I ended up doing this in the client-side
programming language, since
On Fri, Oct 24, 2008 at 10:24 AM, Tony Wasson <[EMAIL PROTECTED]> wrote:
> On Fri, Oct 24, 2008 at 8:04 AM, Steve Midgley <[EMAIL PROTECTED]> wrote:
>> At 11:28 AM 10/23/2008, Joe wrote:
>>>
>>> Steve Midgley wrote:
>
> # (invoiceid, txid)
> (A, 1)
> (A, 3)
> (B, 1)
> (B, 2)
On Fri, Oct 24, 2008 at 8:04 AM, Steve Midgley <[EMAIL PROTECTED]> wrote:
> At 11:28 AM 10/23/2008, Joe wrote:
>>
>> Steve Midgley wrote:
# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)
For journalli
From: "Steve Midgley" <[EMAIL PROTECTED]>
To: "Joe" <[EMAIL PROTECTED]>
Cc: ; "David Garamond" <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2008 4:04 PM
Subject: Re: [SQL] grouping/clustering query
At 11:28 AM 10/23/2008, Joe wrote:
Steve Midgle
At 11:28 AM 10/23/2008, Joe wrote:
Steve Midgley wrote:
# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)
For journalling, I need to group/cluster this together. Is there a
SQL
query that can generate this output:
# (journal: invoiceids, txids)
[A,B] , [1,2,3
Steve Midgley wrote:
# (invoiceid, txid)
(A, 1)
(A, 3)
(B, 1)
(B, 2)
(C, 5)
(D, 6)
(D, 7)
(E, 8)
(F, 8)
For journalling, I need to group/cluster this together. Is there a SQL
query that can generate this output:
# (journal: invoiceids, txids)
[A,B] , [1,2,3]
[C], [5]
[D], [6,7]
[E,F], [8]
Hi
At 10:20 PM 10/22/2008, you wrote:
Message-ID:
<[EMAIL PROTECTED]>
Date: Wed, 22 Oct 2008 12:14:49 +0700
From: "David Garamond" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: grouping/clustering query
X-Archive-Number: 200810/89
X-Sequence-Number: 31731
Dear all,
I have an invoices
Dear all,
I have an invoices (inv) table and bank transaction (tx) table.
There's also the payment table which is a many-to-many relation
between the former two tables, because each invoice can be paid by one
or more bank transactions, and each bank transaction can pay for one
or more invoices. Ex
On Thu, Oct 19, 2006 at 01:51:55PM +0200, Mezei Zolt??n wrote:
>
> Hi,
> I didn't really know what subject I should give.
> I have a table like this one:
> 2006.10.01.Bela10
> 2006.10.01.Aladar9
> 2006.10.01.Cecil8
> 2006.10.01.Dezso7
> 2006.10.01.Elemer6
>
--
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of A. Kretschmer
Sent: Thursday, October 19, 2006 10:18 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Grouping by day, limiting amounts
am Thu, dem 19.10.2006, um 6:55:41 -0700 mailte chester c young folgendes:
> --- "A. Kret
am Thu, dem 19.10.2006, um 6:55:41 -0700 mailte chester c young folgendes:
> --- "A. Kretschmer" <[EMAIL PROTECTED]> wrote:
>
> > > I would like to select the 3 (or 10) names with the most votes for
> > each day.
> > >
> > > Any suggestions on how can it be done easily?
>
> Am I misunderstandi
am Thu, dem 19.10.2006, um 13:51:55 +0200 mailte Mezei Zoltán folgendes:
> Hi,
>
> I didn't really know what subject I should give.
>
> I have a table like this one:
>
> 2006.10.01. Bela 10
> 2006.10.01. Aladar 9
> 2006.10.01. Cecil 8
> 2006.10.01. Dezso 7
> 2006.10.01.
Hi,
I didn't really know what subject I should give.
I have a table like this one:
2006.10.01. Bela
10
2006.10.01. Aladar 9
2006.10.01. Cecil 8
2006.10.01. Dezso 7
2006.10.01. Elemer 6
2006.10.02. Bela 11
2006.10.02. Aladar 10
2006.10.02. Cecil
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> This doesn't give me quite what I'm looking for because I need there
> to be only one of each possible value of seq2 to be returned for each
> value of fkey.
Then perhaps just:
SELECT fkey, seq2, min(seq1)
FROM my_table
WHERE seq2 > 2
GROUP BY fk
This doesn't give me quite what I'm looking for because I need there
to be only one of each possible value of seq2 to be returned for each
value of fkey.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 3
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> What I'd like to be able to do is select all records corresponding to
> the minimum value of seq1 for each value of seq2 corresponding to a
> given fkey (with a lower bound on the value of seq2).
I'm not sure how uid figures in, but would this do
I'm not sure if this is the best thing to do in all occasions, but I have
found a great speed increase using unions over group by.
select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select
min(seq1) from mytable);
union
select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 =
I have a table that looks like this:CREATE TABLE my_table ( pkey serial PRIMARY KEY, fkey int NOT NULL REFERENCES my_other_table( pkey ), uid int NOT NULL REFERENCES user( pkey ), seq1 int, seq2 int);Basically, for each fkey that exists in my_table, there is a sequence represented by
Hi
I wonder if anyone can help me with a SELECT / GROUP BY problem I'm having.
I'm trying to refine a query so that my GROUP BY clause only aggregates rows
that have a non-NULL value in one of the fields, leaving other rows
'ungrouped'.
An example table, resulting from a LEFT JOIN and subselect o
On 2-dec-04, at 3:45, Michael Fuhr wrote:
On Wed, Dec 01, 2004 at 06:57:54AM +0100, Johan Henselmans wrote:
Richard Huxton wrote:
I think what's missing here is the explicit statement of which group
these belong in. Without a value to sort/group by, there's nothing
for
your queries to "get a grip
On Wed, Dec 01, 2004 at 06:57:54AM +0100, Johan Henselmans wrote:
> Richard Huxton wrote:
>
> > I think what's missing here is the explicit statement of which group
> > these belong in. Without a value to sort/group by, there's nothing for
> > your queries to "get a grip on".
> >
> > So - add a "
Richard Huxton wrote:
Johan Henselmans wrote:
Hi, I am having a problem grouping a many to many relationship with
payments and receipts, where a payment can be for multiple receipts,
and a receipt can have multiple payments. I got a list of records that
are involved in such relations, but now I
Johan Henselmans wrote:
Hi, I am having a problem grouping a many to many relationship with
payments and receipts, where a payment can be for multiple receipts, and
a receipt can have multiple payments. I got a list of records that are
involved in such relations, but now I don't know how to grou
Hi, I am having a problem grouping a many to many relationship with
payments and receipts, where a payment can be for multiple receipts,
and a receipt can have multiple payments. I got a list of records that
are involved in such relations, but now I don't know how to group them
so that all paym
Oliver Elphick <[EMAIL PROTECTED]> writes:
> How about:
>SELECT EXTRACT(WEEK FROM trans_date + '1 day'::INTERVAL)
Note that if trans_date is actually a date, you are much better off just
adding an integer to it:
SELECT EXTRACT(WEEK FROM trans_date + 1)
If you add an interval then the date
On Fri, 2004-08-06 at 22:29, Caleb Simonyi-Gindele wrote:
> I'm using
>
> SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY
> trans_date
>
> and it is being used to group sales results by week. It works really well.
>
> What I'm wondering is if I can shift the week from a Mo
I'm using
SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY
trans_date
and it is being used to group sales results by week. It works really well.
What I'm wondering is if I can shift the week from a Mon-Sun
articulation(default with Postgre) to a Sun-Sat sequence. I need it
When grilled further on (Mon, 05 Jan 2004 17:14:26 +),
teknokrat <[EMAIL PROTECTED]> confessed:
> How can I group by date given a timestamp column?
>
I just found this out this weekend. Try 'date_trunc'. Look at secion 9.8.2 of
the documentation. I'm using something like:
SELECT date_tru
How can I group by date given a timestamp column?
thanks
---(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
: "peter" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 24, 2002 2:12 AM
Subject: [SQL] grouping and first()
> Hi, just wondering if anyone can tell me what replaces the first function
> in access.
>
>
> Thanks
>
> PEter
>
>
Hi, just wondering if anyone can tell me what replaces the first function
in access.
Thanks
PEter
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Sat, 13 Oct 2001, Timothy J Hitchens wrote:
> It's been a while since I used postgresql but today I have converted one
> of my web apps but with one small problem. I goto do a group as
> designed and executed in mysql and I get told that this and this must be
> part of the aggreate etc I am p
On Sat, 13 Oct 2001, Timothy J Hitchens wrote:
> It's been a while since I used postgresql but today I have converted one
> of my web apps but with one small problem. I goto do a group as
> designed and executed in mysql and I get told that this and this must be
> part of the aggreate etc I am p
On Sat, 13 Oct 2001 03:32:57 + (UTC), <[EMAIL PROTECTED]> wrote:
> It's been a while since I used postgresql but today I have converted one
> of my web apps but with one small problem. I goto do a group as
> designed and executed in mysql and I get told that this and this must be
> part of th
On Sat, 13 Oct 2001, Timothy J Hitchens wrote:
> It's been a while since I used postgresql but today I have converted one
> of my web apps but with one small problem. I goto do a group as
> designed and executed in mysql and I get told that this and this must be
> part of the aggreate etc I am p
It's been a while since I used postgresql but today I have converted one
of my web apps but with one small problem. I goto do a group as
designed and executed in mysql and I get told that this and this must be
part of the aggreate etc I am puzzled and wonder if someone could bring
me up to stratc
"Graham Vickrage" <[EMAIL PROTECTED]> writes:
> I have written this successfully but need to get the count in time
> increments such as per day/week/month.
Perhaps use GROUP BY date_part(...). See
stgresql.org/devel-corner/docs/postgres/functions-datetime.htm
for doco on date_part().
I am trying to write a select statement to count the occurences of a
particular string between a set of dates.
I have written this successfully but need to get the count in time
increments such as per day/week/month.
At the moment I am doing a select for each increment seperately but figure
that
55 matches
Mail list logo
