From: Oliver d'Azevedo Christina <oliveiros.cristina@asperger-talents.com>
To: Oliver d'Azevedo Christina <oliveiros.cristina@asperger-talents.com>
To: Rihad <rihad@stream.az>
To: pgsql-sql@postgresql.org
Subject: Re: Need help building this query
Date: Thu, 21 Jun 2012 20:18:07 +0100
Message-ID: <B1D3040823F54737AA98F53133DB25A2@Moon> (raw)
References: <4FE35E88.6010805@stream.az>
<40E3F24438264205B10395EFCEE492AD@Moon>
If I understand correctly,
You have table A with a record for each operation performed, perhaps
duplicated.
And you have table B with one record for each operation completed,
Is my understanding correct?
I fail to understand what is the report you are trying to obtain, exactly.
For example,
This line
>> TableA.date_of_op TableB.date_of_op
>> 2012-06-21 2012-06-20 [count(*) and sum(amount) of
>> all data in TableA matched in TableB for the 20-th]
Does it mean that you have operations records in table A from day 21 that
match records from table B for day 20?
The operations shouldn't have already been completed and, thus, without any
record on table A ...?
Could you please kindly elucidate me?
Thank you
Best,
Oliver
----- Original Message -----
From: "Oliver d'Azevedo Christina" <oliveiros.cristina@asperger-talents.com>
To: "Rihad" <rihad@stream.az>; <pgsql-sql@postgresql.org>
Sent: Thursday, June 21, 2012 7:50 PM
Subject: Re: [SQL] Need help building this query
> For matching triples (foo, bar, baz) the date in table B shouldnt always
> be after any date in table A, as table B contains complete operations?
>
>
>
> Best,
> Oliver
>
> ----- Original Message -----
> From: "Rihad" <rihad@stream.az>
> To: <pgsql-sql@postgresql.org>
> Sent: Thursday, June 21, 2012 6:48 PM
> Subject: [SQL] Need help building this query
>
>
>> Hi, folks. I currently need to join two tables that lack primary keys,
>> and columns used to distinguish each record can be duplicated. I need to
>> build statistics over the data in those tables. Consider this:
>>
>>
>> TableA:
>> row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
>> row 2: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
>> row 3: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
>>
>> TableB:
>> row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date
>>
>>
>> Columns foo + bar + baz are used to distinguish a performed "operation":
>> TableA.date_of_op isn't, because it can lag behind TableB.
>>
>> Not all different "operations" are in table B.
>> Table B is just there so we know which "operations" are complete, so to
>> speak (happening under external means and not under any of my control).
>>
>> Now, for each operation (foo+bar+baz) in table A, only *one* row should
>> be matched in table B, because it only has one matching row there.
>> The other two in TableA should be considered unmatched.
>>
>> Now the query should be able to get count(*) and sum(amount) every day
>> for that day, considering that matched and unmatched operations should be
>> counted separately. The report would look something like this:
>>
>> TableA.date_of_op TableB.date_of_op
>> 2012-06-21 [empty] [count(*) and sum(amount)
>> of all data in TableA for this day unmatched in TableB]
>> 2012-06-21 2012-06-20 [count(*) and sum(amount) of
>> all data in TableA matched in TableB for the 20-th]
>> 2012-06-21 2012-06-19 [count(*) and sum(amount) of
>> all data in TableA matched in TableB for the 19-th]
>>
>>
>> Can this awkward thing be done in pure SQL, or I'd be better off using
>> programming for this?
>>
>> Thanks, I hope I could explain this.
>>
>> --
>> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@asperger-talents.com, rihad@stream.az
Subject: Re: Need help building this query
In-Reply-To: <B1D3040823F54737AA98F53133DB25A2@Moon>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox