Having trouble averaging values with a response date in the Select Statement

I need to create a graph for my company that takes the average of all the scores that are taken on a survey for a certain department.

The following is what I have in my query

SELECT     typ.deptType
                      , dept.deptType_id
                     , AVG(cast(acc.score as float)) AS accuracy
                     , AVG(cast(tim.score as float)) AS timeliness
                     , AVG(cast(tude.score as float)) AS attitude
                              , AVG(cast(access.score as float)) as Accessibility
                     , Convert(varchar, acc.responseDate, 101) as responseDate
              FROM   responseDepts dept
              INNER JOIN deptTypes typ ON dept.deptType_id = typ.deptType_id
              LEFT JOIN responseDeptScores acc ON dept.respDept_id = acc.respDept_id
                                 and(acc.scoreType_id = 1)
                         and (acc.score != 0)
              LEFT JOIN responseDeptScores tim ON dept.respDept_id = tim.respDept_id
                       and (tim.scoreType_id = 2)
                       and (tim.score != 0)
              LEFT JOIN responseDeptScores tude ON dept.respDept_id = tude.respDept_id
                      and (tude.scoreType_id = 3)
                      and (tude.score != 0)
                    LEFT JOIN responseDeptScores access ON dept.respDept_id = access.respDept_id
                                    and (access.scoreType_id = 4)
                        and (access.score != 0)
             WHERE dept.responseDate >= #createODBCdate(arguments.fromDate)#
             AND      dept.responseDate < #createODBCdate(arguments.toDate)#
             And dept.deptType_id = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.deptID#">
                group by   typ.deptType
                     , dept.deptType_id, acc.responseDate    

When I put in the response date in the select statement it rounds all the values to whole numbers.  However if I remove the response date I only receive 1 record back in my query with the results being in decimal places.  Does anyone know how I can get the average of the department surveyed for each response date?
Who is Participating?
Brendt HessSenior DBACommented:
It appears that this query is allowing you to use the date/time value in the group by without truncation to ignore time.  I would change the reference to the response date in the GROUP BY statement to use the Converted value, e.g.
GROUP BY typ.deptType,
    Convert(varchar, acc.responseDate, 101)
Ru1995Author Commented:
Wow thanks that looks like it did it.  I didn't know you could insert the functions in the group by clause also
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.