SQL Query To Count Computer Types For A Specified Collection Name

SQL:


Select

CN.Name 'Collection Name',

Case SE.ChassisTypes0

When 1 Then 'Other'

When 2 Then 'Unknown'

When 3 Then 'Desktop'

When 4 Then 'Low Profile Desktop'

When 5 Then 'PizzaBox'

When 6 Then 'Mini-Tower'

When 7 Then 'Tower'

When 8 Then 'Portable'

When 9 Then 'Laptop'

When 10 Then 'Notebook'

When 11 Then 'Handheld Device'

When 12 Then 'Docking Station'

When 13 Then 'All-In-One'

When 14 Then 'Sub-Notebook'

When 15 Then 'Space Saving'

When 16 Then 'Lunch Box'

When 17 Then 'Main System Chassis'

When 18 Then 'Expansion Chassis'

When 19 Then 'Sub-Chassis'

When 20 Then 'Bus Expansion Chassis'

When 21 Then 'Peripheral Chassis'

When 22 Then 'Storage Chassis'

When 23 Then 'Rack-Mount Chassis'

When 24 Then 'Sealed PC'

Else 'Unknown'

End 'Chassis Type',

Count(*) 'Chassis Count'

From v_Collection CN

Join v_FullCollectionMembership CM on CN.CollectionID = CM.CollectionID

Join v_R_System SD on CM.ResourceID = SD.ResourceID

Join v_Gs_System_Enclosure SE on SD.ResourceID = SE.ResourceID

Where CN.Name = 'All Systems'

Group By CN.Name, SE.ChassisTypes0

Order By CN.Name