0

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?

1

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.

5
  • 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 .