% change between 2 columns values

Question

I am trying to get % change between 2 columns values in table. I am bit confused as to why % change is always returned as 0.

-- create
CREATE TABLE Host  
(
    HostName Varchar(100),
    tcCount int,
    PrCount int
);

-- insert
INSERT INTO Host (HostName, tcCount, prCount) 
VALUES ('AppServer04', 125, 5852);
INSERT INTO Host (HostName, tcCount, prCount) 
VALUES ('AppServer05', 300 , 4318);

-- fetch 
SELECT
    HostName, tcCount, PrCount,
    (prCount - tcCount) / prCount * 100 AS 'Change(%)' 
FROM 
    Host

Result I am getting of above is below:

HostName                                          tcCount     PrCount     Change(%)  
---------------------------------------------- ----------- ----------- -----------
AppServer04                                         125        5852           0
AppServer05                                         300        4318           0

(2 rows affected)

For AppServer04 % change should be 97.86 while AppServer05 should be 93.05. I am bit confused as to why I get 0 for both rows..

Am I missing anything here??

Try * 100.0 Note the decimal An INT divided by an INT will return an INT. Also, you may want to trap the dreaded Divide By Zero via nullif( yourdenom ,0 ) — John Cappelletti, Jun 20 '23 at 18:45
Does this answer your question? [SQL division giving wrong answer](https://stackoverflow.com/questions/13163395/sql-division-giving-wrong-answer) AND [What is wrong with this SQL Server query division calculation?](https://stackoverflow.com/questions/11112462/what-is-wrong-with-this-sql-server-query-division-calculation) — Luuk, Jun 20 '23 at 18:55
@JohnCappelletti *100 doesnt work as suggested below op. Issue is data type conversion. I will add NULLIF to avoid divide by zero issue. thanks for your help. — Zulfiqar Dholkawala, Jun 20 '23 at 19:02
@ZulfiqarDholkawala 100.0 (point zero) this will give you an implicit conversion to a float take a peek at https://dbfiddle.uk/bYyZnvTe — John Cappelletti, Jun 20 '23 at 19:24

Martin Brown · Answer 1 · 2023-06-20T19:06:54.203

It's a type thing. Try this:

SELECT
    HostName, tcCount, PrCount,
    CONVERT(decimal, prCount - tcCount) / prCount * 100 AS 'Change(%)' 
FROM 
    Host

If prCount = 5852 and tcCount = 125 then subtracting one from the other gives 5727. 5727/5852 is 0.978639, however SQL Server is thinking this should be an integer so is truncating it at the decimal point to give 0. 0 * 100 is also 0. By converting the first part of the calculation to a decimal it causes the rest of to be done using decimals as well hence the 0.978639 isn't truncated.

% change between 2 columns values

1 Answers1