CODEWITHSUNDEEP
t-sqlsql-server-2012
HitTheSky
HitTheSky

Reputation: 91

T-SQL - how can I use group by on xml objects

I've wrote the following query which I expect to return a data-set as outlined under the query

Query

SELECT 
RelatedRecordID AS [OrganisationID], 
Data.value('(//OpportunityViewEvent/Title)[1]','nvarchar(255)') AS OpportunityTitle,
Data.value('(//OpportunityViewEvent/ID)[1]','int') AS OpportunityID,
Count(Data.value('(//OpportunityViewEvent/ID)[1]','int')) AS Visits
FROM [Audit].[EventData]
LEFT OUTER JOIN Employed.Organisation AS ORG ON [EventData].RelatedRecordID = ORG.ID
Where EventTypeID = 4
Group BY RelatedRecordID
Order By Visits Desc

Expected Result

+-----------------+-----------------+---------------+--------+
| OrganisationID  | OpportunityTitle | OpportunityID | Visits |  
+-----------------+------------------+---------------+--------+
|              23 | Plumber          |           122 |    567 |  
|              65 | Accountant       |            34 |    288 | 
|              12 | Developer        |            81 |    100 | 
|              45 | Driver           |            22 |     96 | 
+-----------------+------------------+---------------+--------+

I receive an error saying

Column 'Audit.EventData.Data' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I then try to group the xml data I get a different error saying

XML methods are not allowed in a GROUP BY clause.

Is there a way to work around this?

Thanks

Upvotes: 2

Views: 285

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can do by adding it into CTE

;with cte as (
SELECT 
 RelatedRecordID AS [OrganisationID], 
 Data.value('(//OpportunityViewEvent/Title)[1]','nvarchar(255)') AS   OpportunityTitle,
 Data.value('(//OpportunityViewEvent/ID)[1]','int') AS OpportunityID,
 Data.value('(//OpportunityViewEvent/ID)[1]','int') as visit
FROM [Audit].[EventData]
LEFT OUTER JOIN Employed.Organisation AS ORG ON [EventData].RelatedRecordID = ORG.ID
Where EventTypeID = 4 )
select OrganisationID, opportunityTitle, opportunityId,  count(visit) as Visits from cte 
Group BY OrganisationID, opportunityTitle, opportunityId

Upvotes: 2

Related Questions