0

I have 4 value in a column. For example;

ColumnA
2.651
3.5
4.55
7.9

And I want to sum it all and cast as a decimal;

 select CAST(sum(ColumnA) as Decimal(LEN(sum(ColumnA)),2)) FROM tablename;

however I am getting this error:

Msg 102, Level 15, State 1, Server dbrank-tsql, Line 7

Incorrect syntax near '('.

When I just run select len(sum(columnA)) which is 5 and type it to;

 select CAST(sum(ColumnA) as Decimal(5,2)) FROM tablename;

it is working. But I cannot always first find the length of this number and then type it to cast.

How can I solve this problem?

Gülşah Demiryürek
  • 11
  • 2
  • 1
    Where is your `SELECT`? `decimal` doesn't allow for an expression for it's precision or scale, it **must** be a literal. You also have more left parathesis (`(`) than right parenthesis (`)`). – Thom A Oct 07 '21 at 08:37
  • okay maybe I should type it this way select CAST(sum(ColumnA) as Decimal(LEN(sum(ColumnA)),2) FROM tablename; – Gülşah Demiryürek Oct 07 '21 at 08:41
  • 1
    No, that won't work, again, the precision and scale *must* be a literal. `LEN(sum(ColumnA))` isn't a literal. – Thom A Oct 07 '21 at 08:46
  • And I also add right parethesis same problem again – Gülşah Demiryürek Oct 07 '21 at 08:47
  • ... I've told you specifically about one of the problem twice... You will continue to get the error because you haven't addressed all the problems. To repeat myself again again: a data type (in this case `decimal`) **cannot** have its length, precision or scale defined by an expression. It ***MUST*** be a ***LITERAL***. For example: `10`, `18`, `2`, or `19`. `LEN(sum(ColumnA))` is an expression and is **NOT** a valid literal value. – Thom A Oct 07 '21 at 08:49
  • Why do you want the value's precision value to vary anyway? That doesn't really make a lot of sense. – Thom A Oct 07 '21 at 08:57
  • Actually I wanted to round this number (sum(ColumnA)) but it always has a lot of zeroes. To remove these zeroes I use "cast as decimal". And when I just run this --> select len(sum(columnA)) from tablename; it returns 5, And I thought maybe I can use it this way. I dont know if there is any other way. – Gülşah Demiryürek Oct 07 '21 at 09:10
  • Round this number to *what*? `5` doesn't have a lot of zeroes, it doesn't have any... – Thom A Oct 07 '21 at 09:11
  • for example -- > select round(sum(columnA),2) from tablename; returns 18,600. But I dont want to zeroes. I just want to 18,6. 5 means length of this number. – Gülşah Demiryürek Oct 07 '21 at 09:15
  • So instead of `18600` you just want `186`? The precision properly of a `decimal` won't change that. Also the value `18600` in a `decimal(5,2)` would error; it's too large (the largest value you can store in a `decimal(5,2)` is `999.99`). – Thom A Oct 07 '21 at 09:22
  • 1
    @Larnu 18,600 means 18.600 in some other locale :) OP is apparently confused by numbers value and presentation - he wants to strip trailing zeroes from decimal part. This is not task for SQL anyway. – Arvo Oct 07 '21 at 10:22
  • 1
    In your presentation layer, you set how `decimal` will be displayed. If you *really* have to do this in SQL, you can do `FORMAT(value, '0.#####')` – Charlieface Oct 07 '21 at 10:31

0 Answers0