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