I have occasionally experienced something operating differently than I expected.
But I see no incorrect behavior of the TOTAL() function in SQLite 3.35.5 . Hence, I think you will need to provide more details allowing others to replicate your results before you can reasonably expect an explanation for them.
Thank you for your response. Apologies, I have just realised the mistake is with the data. This only started happening after I cleaned the data in a table and imported it back in from a CSV file. It turns out I double imported and because I could only see a portion of it. I figured it was an error. Please consider my post withdrawn.