Dividing a float data type value in SQL

Zarena Akbarshah 41 Reputation points
2021-01-25T01:16:22.12+00:00

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

60011-error-worldlandmass-pct-unable-to-revert-0-to-prop.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,561 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-01-25T14:53:51.21+00:00

    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;

    1 person found this answer helpful.

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-01-25T06:18:14.887+00:00

    Hi @Zarena Akbarshah ,

    Welcome to the Microsoft TSQL Q&A Forum!

    Sorry, your description is not clear enough for me.

    60038-image.png

    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:
    60065-image.png

    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.

    0 comments No comments