SQL CAST Float

Question

Post reply

SQL CAST Float

revocats10

SSC Enthusiast

Points: 173
More actions
June 30, 2019 at 2:19 pm

#3658730
Hello all, I am attempting to write a query on a SQL learning site. I am getting an
error with the following query below (I tried many different iterations of this query, but only seem to make it fail worse). Thanks for any assistance/info.
```
SELECT
price,
amount,
CAST(price + amount AS FLOAT) as total FROM items;
```
The error indicates "total" column should be included within results
should be a Float value
Rows should have 3 rows
should return the expected results
Test Failed
the test results also indicate:
Test Results:
Results: Actual Results: Expected
price amount total
10 2 12
15 3 18
20 4 24
May 4, 2021 at 2:54 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

BrownCoat42 SSC Veteran Points: 227 More actions · Answer 1

SELECT

price,

amount,

CAST((price + amount) AS FLOAT) as total FROM items;

You need to enclose the math operation.

Also, as an aside - your training site is probably telling you to do this, but it should be very rare you ever use float data types.

If you are dealing with money, you will usually want to use currency unless you need to with more than about 1 quintillion quantity of the currency, or more than thousandths of the particular currency type, then you will want to use decimal. floating point numbers can be very bad in money, from the nature of the movable significant figure.

If you are dealing with anything else, you will almost always want to use integer, biginteger or decimal data types.

floats and doubles will generally only be used in engineering or physics applications, and more commonly will be doubles.

revocats10 SSC Enthusiast Points: 173 More actions · Answer 2

Thanks very much for the info. I agree with your comment regarding Float.

I tried your method and it is still giving the error with regards to:

"total" column

should be included within results

should be a Float value

Its not really that important to resolve as it is just a training simulator. I am trying to get more learning on SQL and I don't have much experience with CAST or Convert. Thanks.

Thom A SSC Guru Points: 99133 More actions · Answer 3

BrownCoat42 wrote:

You need to enclose the math operation.

You actually don't, the expressions with the mathematical expression in the parentheses is the same and both queries will run:

SELECT CAST(1+2 AS float),
       CAST((1+2) AS float);

Personally, I can't see anything wrong with the statement, but I can't say I know what the actual question being asked it. Perhaps it'#s simply that the website doesn't recognise CASE and you need to use CONVERT? Otherwise really not sure as, like I said, we don't have the full picture here.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 4

It could be a problem with the SQL learning site. Maybe it's expecting a decimal point in the output.

You could try casting it to money:

CAST(price + amount AS MONEY) as total FROM items;

BrownCoat42 SSC Veteran Points: 227 More actions · Answer 5

Thom, I actually didn't check it the other way, but it's sloppy code to not do it. Generally for anyone, queries should be written to exactly control their behavior rather than to get away with the syntax working at that particular time, but is especially important for a new learner. At some point of the code gets changed to A+B*C and they mean ((A+B)*C) it could easily cause a problem.

I didn't catch that he was working in a sim, I bet its the case that they want CONVERT instead of CAST.

To that point for the OP - if that's what the sim wants, that is fine, but generally when you are writing code, you will always want to use ISO SQL rather than proprietary SQL, unless the proprietary SQL - in this case TSQL - provides a benefit. Best example I can come up with off the top of my head is that in converting dates written in text to dates, CAST requires that the formatting of the text date be in an unambiguous format to convert successfully, while CONVERT() would allow you to apply a format in the same function, instead of using another function to format it unambiguously (most likely very messy to do) and then use CAST()

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

Not related to the error the original poster is receiving, but of note, the intended formula is probably:

price * amount

not

price + amount

I'd say the error though is because of the learning site's interpreter. If you want to learn SQL Server, might as well go straight to the horse's mouth and get Express or Developer edition, either would be free:

https://www.microsoft.com/en-us/sql-server/sql-server-downloads