yuro yuro - 4 years ago 134
SQL Question

How to define nested SQL statement to return calculated value?

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?

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download