Month: September 2012

Create a report to count all computer models and manufacturer for computers inside a specific collection

~ Vitalie Ciobanu

SELECT    dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                      COUNT(dbo.v_GS_COMPUTER_SYSTEM.Model0) AS Total
FROM         dbo.v_GS_COMPUTER_SYSTEM INNER JOIN
                      dbo.v_FullCollectionMembership ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_FullCollectionMembership.ResourceID
GROUP BY dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_FullCollectionMembership.CollectionID
HAVING      (dbo.v_FullCollectionMembership.CollectionID = ‘CollectionID’)
ORDER BY Total DESC, Manufacturer, Model