Hi,
I'm trying to modify the built in report "Count all inventoried products and versions" to add "Company Name" to the output. I initially thought this would be simple as the company name is in the same view. However, when I add this to the SQL query I get the error "Column 'PROD.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Microsoft's Working Query
SELECT MIN(PROD.ProductID) AS ProductID, PROD.ProductName, PROD.ProductVersion, Count(distinct PROD.ResourceID) as 'Count'
FROM v_GS_SoftwareProduct PROD
join v_FullCollectionMembership fcm on PROD.ResourceID=fcm.ResourceID
where fcm.CollectionID = @CollID
Group By PROD.ProductName, PROD.ProductVersion
Order by PROD.ProductName
My Broken Query
SELECT MIN(PROD.ProductID) AS ProductID, PROD.ProductName, PROD.CompanyName, PROD.ProductVersion, Count(distinct PROD.ResourceID) as 'Count'
FROM v_GS_SoftwareProduct PROD
join v_FullCollectionMembership fcm on PROD.ResourceID=fcm.ResourceID
where fcm.CollectionID = @CollID
Group By PROD.ProductName, PROD.ProductVersion
Order by PROD.ProductName
Any tips appreciated,
Cheers.
I'm trying to modify the built in report "Count all inventoried products and versions" to add "Company Name" to the output. I initially thought this would be simple as the company name is in the same view. However, when I add this to the SQL query I get the error "Column 'PROD.CompanyName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Microsoft's Working Query
SELECT MIN(PROD.ProductID) AS ProductID, PROD.ProductName, PROD.ProductVersion, Count(distinct PROD.ResourceID) as 'Count'
FROM v_GS_SoftwareProduct PROD
join v_FullCollectionMembership fcm on PROD.ResourceID=fcm.ResourceID
where fcm.CollectionID = @CollID
Group By PROD.ProductName, PROD.ProductVersion
Order by PROD.ProductName
My Broken Query
SELECT MIN(PROD.ProductID) AS ProductID, PROD.ProductName, PROD.CompanyName, PROD.ProductVersion, Count(distinct PROD.ResourceID) as 'Count'
FROM v_GS_SoftwareProduct PROD
join v_FullCollectionMembership fcm on PROD.ResourceID=fcm.ResourceID
where fcm.CollectionID = @CollID
Group By PROD.ProductName, PROD.ProductVersion
Order by PROD.ProductName
Any tips appreciated,
Cheers.