From: Rihad <rihad@stream.az>
To: pgsql-sql@postgresql.org
Subject: Need help building this query
Date: Thu, 21 Jun 2012 22:48:56 +0500
Message-ID: <4FE35E88.6010805@stream.az> (raw)
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.
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: rihad@stream.az
Subject: Re: Need help building this query
In-Reply-To: <4FE35E88.6010805@stream.az>
* 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