0

I want to select the year and quarter parts of the date in a table rather than the whole date. I built the following query to achieve this:

SELECT Clients.ClientID,
       Sum(AccountVals.ValuationAmount) AS SumOfValuationAmount,
       Datepart("YYYY", AccountVals.valuationAmount) AS valYear,
       Datepart("Q", AccountVals.valuationAmount) AS valQuarter
FROM   (Accounts
        INNER JOIN Clients
          ON Accounts.ClientID = Clients.ClientID)
       INNER JOIN AccountVals
         ON Accounts.AccountID = AccountVals.AccountID
GROUP  BY Clients.ClientID,
          SumOfValuationAmount,
          valYear,
          valQuarter;

Although I have successfully used DATEPART in a simple select query the fact that this query is both an aggregate function and have linked tables is throwing me. Any ideas?

EDIT: I realise my query doesn't have any criteria which means it could be trying to aggregate the result from different dates so it can't show the dateparts. I tried adding something like HAVING valYear = 2012 AND valQuarter = 3 but get the same error

harryg
  • 23,311
  • 45
  • 125
  • 198
  • This is because of the order operations are completed in: `FROM, WHERE, GROUP BY, SELECT, ORDER BY`, so for example when you reference `valYear` in the `GROUP BY` Access does not know what this is because it has not yet done the select and realised this is `Datepart("YYYY", AccountVals.valuationAmount)`. The same goes if you tried to reference the alias in the `WHERE` clause. – GarethD Feb 07 '13 at 10:44

1 Answers1

3

I don't think you can use an alias in the group by clause, at least not without a subquery. One solution is to repeat the exact expression in the group by, f.e.:

group by
        Clients.ClientID
,       datepart("YYYY", AccountVals.valuationAmount) 
,       datepart("Q", AccountVals.valuationAmount)
Andomar
  • 232,371
  • 49
  • 380
  • 404