I am having issues with the count aggregate when using a LEFT OUTER JOIN in postgresql 9.3.
When I do a standard statement without the left outer join, it returns the correct count, in this case 3. When the statement gets more complex, like the one below, it returns 7 instead which is incorrect.
Only some of the count() aggregates are incorrect, the majority of them are correct. What is causing this? Should I be using a different join?
SELECT country_code,
period,
COUNT(commissions.id) AS count,
SUM(commissions.total) AS total,
SUM(CASE WHEN commission_adjustments.is_bonus is True THEN commission_adjustments.total else 0 END) AS bonus
FROM commissions
LEFT OUTER JOIN commission_adjustments ON commissions.id = commission_adjustments.commission_id
GROUP BY commissions.country_code, commissions.period
ORDER BY commissions.country_code, commissions.period
COUNT()
counts the number of non-NULL
values. The simplest solution for what you want is to use COUNT(DISTINCT)
:
COUNT(DISTINCT commissions.id) AS count,
This works well if the counts are small and the dimensions are few (there is one dimension here). In other cases, you would want to aggregate the data before joining.