SELECT DomainItemPath,
       SystemItemId,
	   ItemName,
	   ItemTypeId,
	   COUNT(*) as ProjectsUsed
FROM (SELECT DISTINCT si.DomainItemPath,
		   si.SystemItemId,
		   si.ItemName,
		   si.ItemTypeId, -- 4 = System Item, 5 = Enumeration
		   sim.MappingProjectId
	FROM [dbo].[SystemItemMapAssociation] sima
	JOIN [dbo].[SystemItemMap] sim
	  on sima.SystemItemMapId = sim.SystemItemMapId
	JOIN [dbo].[SystemItem] si
	  on sima.SystemItemId = si.SystemItemId
    WHERE si.MappedSystemId = 'TPDM' -- Add Data Standard Id
      --AND sim.MappingProjectId in ('') -- Add in Mapping Projects
	GROUP BY si.SystemItemId,
			 si.ItemName,
			 si.DomainItemPath,
			 si.ItemTypeId,
			 sim.MappingProjectId) items
GROUP BY DomainItemPath,
       SystemItemId,
	   ItemName,
	   ItemTypeId