CODEWITHSUNDEEP
sqlvba
DezyK
DezyK

Reputation: 65

SQL Group By Aggregate Error

Got a more complex SQL, but when testing the a much simplified SQL to get to the bottom of an error I still receive

You tried to excecute a query that does not include the specified expression "datediff("m",0,[StatsDate])" as part of an aggregate function

The SQL I'm testing is:

GetSQL = "SELECT datediff(""m"",0,[StatsDate]) as MonthOffset, SUM([Offered]) as OfferedCount From QueueStats Group By MonthOffset"

So, I am using the expression datediff("m",0,[StatsDate]) in the function by referring to it as MonthOffset in the expression Group by MonthOffset

I'm confused. The expression datediff...... is valid as I've tested that without the Group By.

Upvotes: 0

Views: 163

Answers (2)

Parfait
Parfait

Reputation: 107652

The Jet/ACE SQL engine (which underly MS Access, Microsoft Query, Excel VBA Query, DAO/ADO recordsets of workbooks and text files) does allow named aliases to be used but usually in the SELECT clause. This is mostly due to SQL's order of operations which differs from other languages that are processed in order of code. However, the very first clause SELECT is usually being the last step:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

For instance, the following is a valid query:

SELECT DateDiff('m', 0, [StatsDate]) as MonthOffset, 
       SUM([Offered]) as OfferedCount,
       [MonthOffset] + 5 As FiveDaysAhead,
       [OfferedCount] * 2 As DoubleOfferedCount
FROM QueueStats 
GROUP BY DateDiff('m', 0, [StatsDate])

Upvotes: 0

braX
braX

Reputation: 11755

You cannot use the alias name in your Group By because the query has to be executed before the field gets named.

Use:

Group By datediff("m",0,[StatsDate])

instead.

Upvotes: 3

Related Questions