DMX Query, Group by

I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.

Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?

This is what the DMX query looks like...

SELECT
t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName],
Sum(t.[Profit]) as Profit
From
[Revenue Estimate DT]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName],
[ChildrenStatusName],
[EducationName],
[Profit],
[IncomeName],
[HomeOwnerName],
[SexName],
[Country],
[ProductTypeCode],
[ProductName],
[MailCount],
[OrderAmount],
[SalesAmount],
[MailCost]
FROM
(SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount)
AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey
FROM [DataMining.RevenueEstimate.Predict]
GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID
HAVING (ClientID = 1)) as [Prediction]
') AS t
ON
[Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND
[Revenue Estimate DT].[Education Name] = t.[EducationName] AND
[Revenue Estimate DT].[Income Name] = t.[IncomeName] AND
[Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND
[Revenue Estimate DT].[Sex Name] = t.[SexName] AND
[Revenue Estimate DT].[Country] = t.[Country] AND
[Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND
[Revenue Estimate DT].[Product Name] = t.[ProductName] AND
[Revenue Estimate DT].[Mail Count] = t.[MailCount] AND
[Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND
[Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND
[Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND
[Revenue Estimate DT].[Profit] = t.[Profit] AND
[Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName]
GROUP BY t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName]

Hello

GROUP BY is not supported in DMX, and neither are temporary tables. A solution would be to execute the query, store the results inside SQL Server, then execute the group by inside the relational engine.

You can find some details on executing predictions from the relational engine in this article: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx

|||Or you could do as Bogdan suggests without storing the results and performing SQL operations on an OPENROWSET DMX query result