Postgresql incorrect COUNT() value with left outer join

Question

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

You probably want count(commission_adjustments.commission_id) but without some sample data and what you expect this is hard to tell — user330315
– user330315, Commented Mar 26, 2020 at 13:03
You are correct, I've edited it now. Yes, I understand it's hard without sample data. I was hoping something might be glaringly obvious with the statement itself. — Olis
– Olis, Commented Mar 26, 2020 at 13:07

Gordon Linoff · Accepted Answer · 2020-03-26 13:19:10Z

1

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.

answered Mar 26, 2020 at 13:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Olis Over a year ago

This was the issue in this case. Thank you so much

Caius Jard · Accepted Answer · 2020-03-26 13:30:40Z

If you have this:

SELECT * FROM table
id, x
1, 'foo'
2, 'foo'
3, 'foo'

SELECT x, COUNT(*) as ct FROM table GROUP BY x
x, ct
'foo', 3

And it's "correct"..

And then you do this:

SELECT x, COUNT(*) as ct FROM table LEFT JOIN sometable ON table.x = sometable.y GROUP BY x

And it suddenly "goes wrong":

x, ct
'foo', 7

It's because there is a cartesian product; more than one row of sometable matched with this row. Remove the grouping/put a select * and have a look:

SELECT * FROM table LEFT JOIN sometable ON table.id = sometable.otherid --GROUP BY x

id, x, otherid
1, 'foo', 1
1, 'foo', 1
1, 'foo', 1
2, 'foo', 2
2, 'foo', 2 
2, 'foo', 2
3, 'foo', null

7 rows, caused by:

SELECT * FROM othertable
otherid
1
1
1
2
2
2

More than one row from othertable matches a row in table

You need to either restrict your join, so it isn't causing this multiplying of rows, or perhaps group your joining rows in a subquery before you do the join

Switching JOIN type can also cause more rows to appear if suddenly not all rows have to match any more, but it wouldn't affect this case where you're counting a table on the left and adding another via left join; only Cartesian products will bump things up here

alboforlizo · Accepted Answer · 2025-01-02 13:38:42Z

Caius Jard tried to explain why the row count changes with JOIN but you can see the problem with a single table as

SELECT * FROM table
id, x
1, 'foo'
2, 'foo'
3, 'foo'

SELECT x, COUNT(*) as ct FROM table GROUP BY x
x, ct
'foo', 3

but

SELECT * FROM table
id, x, y
1, 'foo', 1
2, 'foo', 2
3, 'foo', 3

SELECT x, y, COUNT(*) as ct FROM table GROUP BY x, y -- notice GROUP BY y is compulsory if you SELECT both x and y
x, y, ct
'foo', 1, 1
'foo', 2, 1
'foo', 3, 1

The JOIN clause just melds two or more table together according to the ON condition and can produce a table similar to last case above or event worse such as the following:

SELECT * FROM table
id, x, y
1, 'foo', 1
2, 'foo', 1
3, 'foo', 2
4, 'foo', 3
5, 'foo', 4

SELECT x, y, COUNT(*) as ct FROM table GROUP BY x, y -- notice GROUP BY y is compulsory if you SELECT both x and y
x, y, ct
'foo', 1, 2
'foo', 2, 1
'foo', 3, 1
'foo', 4, 1

Collectives™ on Stack Overflow

Postgresql incorrect COUNT() value with left outer join

3 Answers 3

1 Comment

You need to either restrict your join, so it isn't causing this multiplying of rows, or perhaps group your joining rows in a subquery before you do the join

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

You need to either restrict your join, so it isn't causing this multiplying of rows, or perhaps group your joining rows in a subquery before you do the join

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related