SQL - convert int to double

Question

I have two columns of type bit in my sql table, and the values ​​in it are similar,

 FirstHalfLeave SecondHalfLeave ------------ ------------- 0 1 1 1 

I need to sum these two fields to leave it for one day, and I need to output the exact result (3/2=1.5)

I just converted these bit to integer as

 sum(CAST(StaffAttendance.FirstHalfStatus as Integer) + CAST(StaffAttendance.SecondHalfStatus as integer))/2 as TotalLeave 

it shows the result as 1 not 1.5 , for this I think I need to drop it twice or swim, I don’t know how to do this, can someone help me, thanks in advance

Answer 1

use /2.0 instead to create a decimal type

Edit:

To remove trailing zeros other than float . And I think what you set ) wrong. Try:

 select cast( sum( ( CAST(StaffAttendance.FirstHalfStatus as Integer) + CAST(StaffAttendance.SecondHalfStatus as integer) ) / 2.0 ) as float ) as TotalLeave from your_table 

Answer 2

The easiest way to convert to double is to multiply by 1.0 as follows:

  1.0 * (sum(CAST(StaffAttendance.FirstHalfStatus as Integer) + CAST(StaffAttendance.SecondHalfStatus as integer)))/2 as TotalLeave 

Answer 3

Either divide by 2.0 , or multiply by 0.5 .

Answer 4

Have you tried to distinguish between bit columns and float instead of ints?

 sum(CAST(StaffAttendance.FirstHalfStatus as float) + CAST(StaffAttendance.SecondHalfStatus as float))/2 as TotalLeave 

Answer 5

to try

  Sum(Case Cast(FirstHalfLeave + SecondHalfLeave as Numeric)) / Count(*)