Different value when calculating time average on cte versus non cte

Question

I am building a SQL query and I need to find the average of time. I can do this from either a DateDiff, or a have a field that already calculates the time without needing a DateDiff. When I use a standard query I get a different average amount than when I use a CTE and do the average from the of a list of data in the CTE. Why does this happen and which one is more accurate? The CTE average matches calculations that occur in excel on the same data set.

Standard example:

SELECT
AVG(TIMEFIELD) AS AVERAGETIME
FROM
VIEW
FROM
VIEW

CTE Example
WITH CTE AS(
SELECT
TimeField
FROM
View
)
select 
avg(timefield) as averagectetime

Answer 1

I would expect the two queries to return the same result, but then again I don't see the actual queries, nor do I know the actual view definition.

If you can produce a simple example that we can run, it will be easier to answer. (That is, this would be a script that creates table(s), inserts data and runs the queries.)

Answer 2

Hi @Tamayo, Ashley

What's the datatype of TIMEFIELD?

My guess is, there might be some filters in your CTE. Could you please post the original code along with some sample records?

Best regards,

Cosmog Hong