How to define nested SQL statement to return calculated value?

Question

The problem is as follows. I have the following SQL statement:

// SQL statement for ms access 2010

SELECT 

    ID, SUM(fund_assets) AS Sum_FV, COUNT(*) AS DataCount, 

    (Sum_FV / DataCount) AS Result

FROM 

    V_Assets_L12

WHERE 

    Datum >= DATEADD("m", -12, #6/2/2017#) 

    AND Datum <= #6/2/2017# 

    AND ID = 325

GROUP BY 

    ID

ORDER BY 

    ID;

The result is correct for

ONE

ID! But I need a list of all existing IDs of V_Assets_L12. So that my first idea was to define a nested sql statement as follows:

SELECT 

    ID, SUM(fund_assets) AS Sum_FV, COUNT(*) AS DataCount, 

    (Sum_FV / DataCount) AS Result

FROM 

    V_Assets_L12

WHERE 

    Datum >= DATEADD("m", -12, #6/2/2017#) 

    AND Datum <= #6/2/2017# 

    AND ID IN (SELECT DISTINCT ID

               FROM V_Assets_L12

               ORDER BY ID ASC)

GROUP BY 

    ID

ORDER BY 

    ID;

But this doesn't work for me. What did I wrong?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You can just remove the condition in the WHERE clause:

SELECT ID, SUM(fund_assets) AS Sum_FV, COUNT(*) AS DataCount,
       (Sum_FV / DataCount) AS Result
FROM V_Assets_L12
WHERE Datum >= DateAdd("m", -12, #6/2/2017#) And
      Datum <= #6/2/2017#
GROUP BY ID
ORDER BY ID;

How to define nested SQL statement to return calculated value?

Sign up