bammerr
bammerr

Reputation: 43

Rounding a Column in SQL

I ran the following query:

SELECT 
    year, month_name, 
    south / (south + west + midwest + northeast) * 100.0 AS perc_south, 
    west / (south + west + midwest + northeast) * 100.0 AS perc_west, 
    midwest / (south + west + midwest + northeast) * 100.0 AS perc_midwest, 
    northeast / (south + west + midwest + northeast) * 100.0 AS perc_northeast
FROM 
    tutorial.us_housing_units 
WHERE 
    year > 2000
ORDER BY 
    year DESC;

It returns percentages that span many decimal places.

How can I round the resulting columns to (let's say) 4 decimal places?

Upvotes: 1

Views: 447

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

In my opinion, the best way is to convert to decimal:

convert(decimal(6, 2), south/(south+west+midwest+northeast)*100.0)

This actually stores the value using two decimal places. Downstream applications generally respect the format.

You can also use the round() function to round the value (although this does not change the type). Or use the str() function to convert the value to a string.

Upvotes: 2

Related Questions