How to divide two columns?

I tried to divide two columns from joined tables but the result (value of column relative_duration) is always 0. The query is the following:

    SELECT t1.[user_1]
      ,t1.[user_2]
      ,t1.[total_duration]
      ,(t1.total_duration/t2.[total_events_duration]) AS relative_duration
  FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 INNER JOIN [CDRs].[dbo].[user_events_monthly_stats] AS t2 ON t1.[user_1] = t2.[user_1]

Does anyone know what could be wrong in the query above and how to fix it in order to divide column total_duration from table t1 with column total_events_duration from table t2?

BTW I tried to replace division with subtraction (“/” with “-“) and in that case the column relative_duration is not 0.

Answer

Presumably, those columns are integer columns – which will be the reason as the result of the calculation will be of the same type.

e.g. if you do this:

SELECT 1 / 2

you will get 0, which is obviously not the real answer. So, convert the values to e.g. decimal and do the calculation based on that datatype instead.

e.g.

SELECT CAST(1 AS DECIMAL) / 2

gives 0.500000

Advertisement

Answer