Divide integer columns help

Transact-SQL
1

i have 2 integer columns in my employee table
a. day_difference (basically does a datediff on my start_date and end date =
datediff(day, start_date, end_date ).
b) counts(does a count on my total transactions) = count(distinct trans_id)
after i run my main query i get the below

lets say i have the table populated like this

name, trans_id, location, counts , date_difference
jane 5412 east 1 6
jane 5412 west 1 6
jane 5412 south 1 6
joe 5214 south 1 4
joe 5214 north 1 4

NOW WHAT I WANT TO DO IS GET THE AVERGE DATE DIFFERENCE AND DIVIDE BY TOTAL TRANSACTION PER TRANS_ID
WHEN I DO THIS
select trans_id, location AVG(DATE_DIFFERENCE)/ sum(counts) 'Avg' FROM EMPLOYEE
GROUP BY name,trans_id, location

I WOULD EXPECT TO GET
JANE 5412 2 ( the avg for jane is 6 and her total counts = 3)
joe 5214 2 )the avg for joe is 4 and her total is 2 )
however i get this JANE 5412 6 And JOE 5214 4 using the same select query BOTH, date_difference AND COUNTS columns are integers

ALSO if the avg divide by count results in a decimal i would like to round it to the nearest WHOLE number EXAMPLE AVG = 19 DIVIDE BY COUNTS 4 = 6.333333 ROUNDED TO 6

2

Hello,
(ok for MS SQL SERVER)
Try convert to decimal (18,2) all data involved.
for example if you query select 3/2 youll get : 1
if you query cast(3 as decimal(18,2)) / cast(2 as decimal(18,2))
you'll get 1.50000000000

cast(cast(3 as decimal(18,2)) / cast(2 as decimal(18,2)) as decimal(18,2))
will result: 1.50

also:
select 3.00/2.00 will return 1.500000

be carefull with data type.... :wink:

also if you do a sum and a NULL value is processed, result is null
so, better be sure null is translated to 0
isnull(counts,0)

select trans_id, location, AVG(cast(DATE_DIFFERENCE as decimal(18,2)))/ cast(sum(isnull(counts,0)) as decimal18,2)) 'Avg' FROM EMPLOYEE
GROUP BY name,trans_id, location

hope this help