Forum Stats

  • 3,768,195 Users
  • 2,252,758 Discussions
  • 7,874,486 Comments

Discussions

sql query to perform SUM on String data type

User_X9S6M
User_X9S6M Member Posts: 54 Red Ribbon

In a table have three columns of data type string with their values as decimals. Need sql query to perform SUM for these columns.

SALES_QTY           SALES_REVENUE_SETTING_CURRENCY  SALES_REVENUE_SITE_CURRENCY

6679642.000000000    128889.721766052                        14520998.854682000

6.000000000          40092.211517913                         264187.628566338

can someone help?

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    edited Jun 7, 2021 1:32PM

    Hi, @User_X9S6M

    In a table have three columns of data type string with their values as decimals

    Why are you storing them as strings? Wouldn't it make more sense to use NUMBER columns?

    SUM only works on NUMBERs. If you're stuck with numbers stored in string columns, you can use TO_NUMBER to convert them into NUMBERs, then do the SUM, for example

    SUM (TO_NUMBER (sales_qty DEFUALT NULL ON CONVERSION ERROR))
    

    depending on your Oracle version.

    I hope this answers your question. If not, post a little sample data (CREATE TABLE and INSERT statements), the exact results you want from that data, and an explanation of how you get those results from that data. Alwyas say which version of Oracle you're using (e.g., 12.2.0.1.0),