Skip to content
Advertisement

Simpler way to do a SUM with a fanout on a join

Note: SQL backend does not matter, any mainstream relational DB is fine (postgres, mysql, oracle, sqlserver)

There is an interesting article on Looker that tells about the technique they use to provide correct totals when a JOIN results in a fanout, along the lines of:

A good way to simulate the fanout it just doing something like this:

Their example does something like this, which I think is just a long-form way of grabbing md5(PK) with all the fancy footwork to get around the 8-byte limitation (so they do a LEFT(...) then a RIGHT(...):

Is there another general-purpose way to do this? Perhaps using a correlated subquery or something else? Or is the above way the best known way to do this?

Two related answers:


Without worrying about a general-purpose hashing function (for example, that may take strings), the following works:

But this still begs the question: is there another / better way to do this in a very general-purpose manner?

Advertisement

Answer

A typical example for the joins mutilating the aggregation is this:

where both counts result in the same number, because each gets multiplied by the other. With 2 likes and 3 dislikes, both counts are 6.

The simple solution is: Aggregate before joining. If you want to know the likes and dislikes counts per post, join the likes and dislikes counts to the posts.

Use COALESCE, if you want to see zeros instead of nulls.

Don’t try to muddle through with tricks. Just aggregate, then join. You can of course replace the joins with lateral joins (which are correlated subqueries), if the DBMS supports them. Or for single aggregates as in the example even move the correlated subqueries to the select clause. That’s mainly personal preference, but depending on the DBMS’s optimizer one solution may be faster than the other. (Ideally the optimizer would come up with the same execution plan for all those queries of course.)

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement