Link to home
Start Free TrialLog in
Avatar of Ru1995
Ru1995Flag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ru1995

ASKER

Wow thanks that looks like it did it.  I didn't know you could insert the functions in the group by clause also