Re: BUG #3958: Self-Join Group-By Clause Produces Incorrect Results

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Marcus Torres wrote:
>> I wrote a simple self-join query to sum the transaction count of different
>> types of records in a audit table and the result set for the different sum
>> totals was the same which is incorrect.

> Looks perfectly correct to me.

Me too.  The underlying data before grouping/aggregation is

regression=# select
     A1.AUDIT_DATE, P.CONTENT_POLICY_NAME, A1.TXN_COUNT, A2.TXN_COUNT
FROM T_AUDIT A1,
     T_AUDIT A2,
     T_POLICY P
WHERE P.ID = A1.POLICY_ID
  AND P.ID = A2.POLICY_ID
  AND A1.POLICY_ID = A2.POLICY_ID
  AND A1.AUDIT_DATE = A2.AUDIT_DATE
  AND A1.AUDIT_TYPE_CODE = 'CONTENT_1'
  AND A2.AUDIT_TYPE_CODE = 'CONTENT_2';
 audit_date | content_policy_name | txn_count | txn_count
------------+---------------------+-----------+-----------
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
 2008-01-01 | TEST POLICY         |         1 |         1
(10 rows)

from which it's clear that given all ones in txn_count, the sums *must*
be the same because they're taken over the same number of rows.

I suspect what the OP needs is two separate queries (perhaps union'ed
together) not a self-join.

            regards, tom lane