Unable to Group By calculated field

[al3sha]

Thank you for looking at my problem. 

Here is my SQL statement:

SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]' 
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
ORDER BY QV.WF_tbl_Chapter_Position, QV.WF_tbl_Section_Position, QV.WF_tbl_Question_Position
GROUP BY ????
for xml path(N'')

It works fine so long as there is no GROUP BY statement. I've tried using DISTINCT in the SELECT statement but it then returns empty set.  I want to be able to GROUP BY to save time parsing the XML.

Any help/thoughts is appreciated. 

Thank you.

[CoopperBackpack]

Hello @al3sha,

I tested the similar statement in my account and the formula is valid. So, it is possible to use this kind of statements in Calculated value field. 

1) Please use the GROUP BY clause before the ORDER BY clause.

2) Please note that you need to include in the GROUP BY clause all non-aggregated fields that you added to the SELECT statement. In this example you do not use aggregate functions, so all 3 fields should be included in the GROUP BY clause.

3) In this case you need to add the fields to the ORDER BY clause that are contained in either an aggregate function or the GROUP BY clause.

Hope this helps. 

[Flowers4Algernon]

Hello, just wanted to share this how-to article that could be helpful: https://howto.caspio.com/function-reference/