DEV Community

Discussion on: 4 Ways to Calculate a Running Total With SQL

Collapse
geraldew profile image
geraldew

At the risk of being tedious, I'll point out that your use of the analytic function assumes a particular default in the SQL dialect. For your example to be unambiguous you would need to add "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW".

I've covered this previously in a similar response on dev.to - see dev.to/helenanders26/sql-301-why-y...

While this can seem to be nitpicking, I think it tells us all that we can easily mistakenly think we know a feature of the SQL language when in fact we just know a feature of some implementations. i.e. the implementation is applying a default ROWS clause whether we realise it or not.

On another point, I have found a main reason for not using analytic functions occurs when they're simply not there in the dialect. You might be surprised how many of these are around in all sorts of products.

You've done a good job here of describing alternatives, I expect someone somewhere sometime will find themselves needing that option and will be grateful for your examples.

Collapse
seattledataguy profile image
SeattleDataGuy Author

It's very true!

That is why I provided multiple ways to perform the action! Not just using an analytic function. Analytic functions just tend to be the quickest and easiest to understand.