SELECT 
COUNT(*) AS RecNum,
  CASE CS.Manufacturer0
    WHEN 'Hewlett-Packard' THEN 'HP'
	WHEN 'Dell Inc' THEN 'DELL'
	WHEN 'Dell, Inc' THEN 'DELL'
	WHEN 'Dell, Inc.' THEN 'DELL'
	WHEN 'Dell Inc.' THEN 'DELL'
	WHEN 'DELL__' THEN 'DELL'
	WHEN 'Intel Corporation' THEN 'INTEL'
	WHEN 'Intel(R) Client Systems' THEN 'INTEL'
	WHEN 'To be filled by O.E.M.' THEN 'MISSING'
	WHEN 'System manufacturer' THEN 'MISSING'
	WHEN 'Default string' THEN 'MISSING'
	WHEN 'Microsoft Corporation' THEN 'MICROSOFT'
	WHEN 'Microsoft Corp' THEN 'MICROSOFT'
	WHEN 'VMware, Inc.' THEN 'VMWARE'
	WHEN 'Panasonic Corporation' THEN 'PANASONIC'
	WHEN 'Panasonic Corp' THEN 'PANASONIC'
	WHEN 'Gigabyte Technology Co., Ltd.' THEN 'GIGABYTE'
	WHEN 'TANGENT COMPUTER' THEN 'TANGENT'
	WHEN 'Supermicro' THEN 'SUPERMICRO'
	WHEN 'Cybernet Manufacturing Inc.' THEN 'CYBERNET'
	WHEN 'Eurotech, Inc.' THEN 'EUROTECH'
	WHEN 'Touch Dynamic Inc.' THEN 'TOUCH DYNAMIC'
	WHEN 'Dedicated Computing' THEN 'DEDICATED COMPUTING'
	WHEN 'Omni Tech Corp' THEN 'OMNITECH'
	WHEN 'Advantech Co.,Ltd.' THEN 'ADVANTECH'
	ELSE CS.Manufacturer0
  END AS 'Manufacturer',
  CS.Model0 AS 'Machine Model'
FROM  
  v_GS_COMPUTER_SYSTEM CS
WHERE
  CS.Manufacturer0 NOT LIKE 'Lenovo'
GROUP BY 
 CS.Manufacturer0, 
 CS.Model0
 
UNION
 
SELECT COUNT(*) AS RecNum,
 CS.Vendor0 AS 'Manufacturer', 
 CS.Version0 AS 'Machine Model'
FROM  
  v_GS_COMPUTER_SYSTEM_PRODUCT CS
WHERE
	CS.Vendor0 LIKE 'Lenovo'
GROUP BY 
 CS.Vendor0,
 CS.Version0
ORDER BY RecNum DESC, [Manufacturer] ASC, [Machine Model] ASC