SQLite Forum

ORDER BY not working for a specific DB/table
Login
Adopting for the moment your goop versus stuff distinction, which I fit into an objects versus substance categorization, I think the concern and issues with cent-exactitude are related to it.  If cents were merely being added, (or multiplied by integers, which is conceptually the same), or subtracted, there would be little difficulty presented for many programmers. It is when money is treated as a non-granular substance, by application of interest rates (often compounded daily) or other very fine-grained or no-grained operations, that arbitrary fractions of the monetary unit are computed and notions of exactitude become a conceptual pitfall. Coupled with the demands of double-entry accounting, where quantities are expected to be exact so that certain errors can be detected, that is a recipe for confusion and woe.

The problem would be better addressed by recognizing distinct money types: one which is always "exact" and granular ("stuff"), and participates in whatever exact accounting scheme is being implemented; and the other allowed to be inexact according the actual precision demanded by real-world concern thresholds, and as fine-grained ("goop") as cost-effective representations (such as 64-bit floating point) support. With such a distinction made, it would become fairly clear where rounding should and should not be done within money processing applications.

Getting back to the current topic, I would ask the OP: Why are you storing quantities with extra bits that you do not consider meaningful enough to display and participate in ordering or grouping? Perhaps rounding to cents should have been done going into the DB, and if so, it would be simpler to represent them exactly rather than lamenting the fact that floating point cannot represent decimal fractions exactly.