"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
