Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Modify built in report "Count all inventoried products and versions"

Status
Not open for further replies.

BrianH1

MIS
Aug 29, 2002
82
GB
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top