sql cast syntax

Question

Having trouble with cast syntax. I get an error message with this and I am having trouble figuring out what the problem is. I'm using pandasql.

SELECT CAST((((male_year_df.total_male_suicides) / female_year_df.total_female_suicides)) AS DECIMAL(1,5)) AS male_female_ratio), male_year_df.year

EDIT: this is the error message: OperationalError: near ")": syntax error

"I get an error message" why are you making us guess what the error message is? Include it in your question. Also, what is a DECIMAL(1,5), 1 digit long, 5 of which on the right side of the decimal point? Makes little sense — HoneyBadger
– HoneyBadger, Commented Sep 11, 2022 at 18:02

nbk · Accepted Answer · 2022-09-11 18:20:42Z

1

your parethesis are too much of, also DECIMAL(1,5) is not allowed as the first number has to be equal or bigger than the second number

SELECT CAST(
            
              (
                (male_year_df.total_male_suicides) 
                / (female_year_df.total_female_suicides)
            ) AS DECIMAL(5,5)) AS male_female_ratio
              , male_year_df.year

You can first define the firsst number with the precision you need and then divide the second number, so that the engine will not use integer division

 SELECT CAST(male_year_df.total_male_suicides AS DECIMAL(5,5)) 
     / (female_year_df.total_female_suicides) AS male_female_ratio
              , male_year_df.year

edited Sep 11, 2022 at 18:20

answered Sep 11, 2022 at 18:06

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bijan Khair Rahman Over a year ago

it works, but it's giving me whole numbers and rounding them, how do I get it to have 5 places after the decimal point?

Bijan Khair Rahman Over a year ago

so I use AS FLOAT instead of decimal and it's giving me values like 2.0 and 3.0, it's not giving me the precise numbers after the decimal.

nbk Over a year ago

then use DECIMAL(5,5) on the first numnber so that Integer dision will not be used

Bijan Khair Rahman Over a year ago

ok that worked. I used float instead of decimal, but yeah I used it on the first number. thanks!

Collectives™ on Stack Overflow

sql cast syntax

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related