This question already has answers here:
SQL Server, division returns zero (7 answers)
Closed 29 days ago.
I am trying to add a percentage column to my table but my results are funky with the sql below:
update code_data
set code_red_percent = case when red_code_count = 0 then 0
else (red_code_count / total_code_count) * 100
end
It seems to be populating by percent column with 100 any time that the total_code_count and red_code_count match, but any time that the numbers are different it updates the percent column to a 0. What am I missing here?
2条答案
按热度按时间mum43rcc1#
Looks like you are hitting integer division; multiply first:
yqyhoc1h2#
First, we create a table and populate it with some example data (it's helpful if you provide this when asking a question).
The first
SELECT
shows two calculations. The first usingINT
values and the secondDECIMAL
values. You can see theINT
values are returned as a roundedINT
. TheDECIMAL
values return the expected result.We can use the same method to perform the
UPDATE
if that's what you really want.In the initial table definition I also included another column without a data type, instead providing a definition for the calculation required to get the value. This is pretty much the same as the calculation used in the select, except it's automatically handled by the engine, and will always be up to date if the values it's derived from ever change. This is a computed column: https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16
| code_red_count | total_code_count | code_red_percent | code_red_percent_upd |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 10 | 0.1000000000000 | 0.10 |
| 2 | 10 | 0.2000000000000 | 0.20 |
| 3 | 10 | 0.3000000000000 | 0.30 |
| 50 | 10 | 5.0000000000000 | 5.00 |