• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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?
1 Solution
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now