Dividing a float data type value in SQL

Question

Hello MS Community,

I am facing some difficulty in constructing the appropriate syntax to derive the right result.

The issue is as follows:

1. In my 1st syntax I mistakenly round up the WorldLandMass_pct to "0"
2. I tried to convert it back unfortunately the 2nd systax is not allowing me.
3. The data type for the WorldLandMass_pct column is in INT

SELECT c.*, ROUND((("2020"-"2019")/"2019"),5) AS GrowthRate_pct, ROUND(c."2020"/a.Land_Area_Km_sq,0) "Density", Land_Area_Km_sq, WorldLandMass_pct, SubRegName, SDGRegName, GeoRegName
INTO Table2
FROM CountryPopYoY c
INNER JOIN LandArea a
ON c.Country = a.Country
INNER JOIN LocationMapping m
ON c.Country = m.Location
ORDER BY Land_Area_km_sq DESC;
SELECT * FROM TABLE2

declare @v1 int;
select @v1= vv from (select SUM (Land_Area_km_sq) as vv from Table2 )as ss
select @v1

UPDATE Table2
SET WorldLandMass_pct = Land_Area_km_sq / 130094783
where WorldLandMass_pct = 0;
select * from Table2;

See attached for the table and the screenshot.

59983-table2.txt

Answer 1

You are dividing an integer by an integer which results in an integer.

Please try:
UPDATE Table2
SET WorldLandMass_pct = Land_Area_km_sq / 130094783.00
where WorldLandMass_pct = 0;
select * from Table2;

Answer 2

Hi @Zarena Akbarshah ,

Welcome to the Microsoft TSQL Q&A Forum!

Sorry, your description is not clear enough for me.

I did not find the code to round up the WorldLandMass_pct to "0" in your code.I saw the following two codes:

    ROUND(c."2020"/a.Land_Area_Km_sq,0) "Density"  
    SET WorldLandMass_pct = Land_Area_km_sq / 130094783  

So, I guess your problem should be: In the first select statement, you made a wrong setting, so you want to use the update statement to reset.

If so, you don't need to use the update statement to set, because the select statement does not change the source data in the table, it just presents the data in the format you set. You only need to reset it in the first select statement.

    ROUND(c."2020"/a.Land_Area_Km_sq,0)  

I don’t know if the rounding to 0 you mentioned refers to this statement.
The 0 in round means that the number of decimal places after rounding is 0.
Refer to the following example:

    SELECT ROUND(123.9994, 3), ROUND(123.9994, 2),ROUND(123.9994, 0)    

Output:

In addition, when you post a question, just provide us with a minimal example,this includes your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result. So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.