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

Posted on 2011-10-11
Last Modified: 2012-05-12
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?
Question by:Ru1995
    LVL 32

    Accepted Solution

    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)

    Author Closing Comment

    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them ( . I did keep the main js functions but made sever…
    Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now