I am using SELECT SUM to sum up all stocks values in the portfolio. It works fine as long as the values in database are not converted into USD. However, it returns 0 if values are updated in database as USD. Is there any way to make SELECT SUM work with USD values?


SUM is meant to work on numbers. When you talk about "USD values", do you store numbers in text fields? If so, don't.

The $ or USD or whatever should be kept separate from the number itself. In a multi-currency environment, you could store it in a separate column, but here the only currency is USD, so there's no reason for that.

The dollar formatting should be done only when displaying (in the template, like {{ cash | usd }}, but not in the database.

  • Thank you. I store values as numeric and update database in a way so that “stock in stocks” as hinted in Zamyla's example works when printing everything out. There are a number of ways to solve this challenge (additional varible, additional column in db, anorther cycle, etc) but I thought that maybe there is a smart way to make SELECT SUM work with USD values. :) – Ivan Martynov Sep 13 '17 at 16:01
  • What exactly is a "USD value" to you? A number, to be interpreted as USD? SUM should work. A string like "$47.11"? Maybe there's something like CAST(RIGHT(usd_val, LENGTH(usd_val)-1) AS NUMBER) to strip that $ and convert to number, so you could sum it up. – Blauelf Sep 13 '17 at 16:11
  • Yes, a string in a format $47.11. I apply the provided in helpers.by usd function, which converts values into "${:,.2f}".format(value)”. Thank you for pointing me to CAST. I need to read about it. – Ivan Martynov Sep 13 '17 at 16:19
  • usd is meant to be used in the template, formatting the number for presentation. That's why they registered it with the template engine. – Blauelf Sep 13 '17 at 16:22
  • Thank you. Just figured out that all this complexity with conversion is not needed as you can turn values into usd format directly inside the html file :) – Ivan Martynov Sep 16 '17 at 9:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .