Search code examples
sql-serverdecimal-point

Convert(Decimal(10,2),Sum(coulmn1)/Sum(coulmn2)) showing 0 data but both coulmn1 and coulmn2 has values

I have a table with fields TalkInTime and LoggedOnTime both has '9806' and '2048810' records respectfully. I want to take Percentage of TalkInTime so I queried

Select CASE WHEN LoggedOnTime=0 THEN 0 ELSE (SUM(TalkInTime)*100/SUM(LoggedOnTime)) [%TalkInTime] End From Tablename

It's showing 0 as result but the actual value it should show is 0.478. So I guess It would be the decimal point problem so I changed the query as

Select CONVERT(DECIMAL(10,2),SUM(ASGI.TalkInTime)/SUM(ASGI.LoggedOnTime)) from Tablename

But it's showing 0 record. Where am I missing the correct syntax? Can anyone please help me?

Solution

  • You need to convert the numerator first and then divide. Otherwise, you are converting the results of integer division, which will already be 0 when you pass it to convert:

    Select CONVERT(DECIMAL(10,2),SUM(ASGI.TalkInTime))/SUM(ASGI.LoggedOnTime) 
from Tablename

    Demo

    Note that / "Returns the data type of the argument with the higher precedence" as per MSDN. Therefore, you would not need to change the query if either of your input values were floating point. However, since both values are integers, the division result is also an integer and the decimal part is truncated.