Chart Report for Physical vs Virtual

[cbtg2006]

I'm looking to create a report that report illustrates the physical/virtual ratio in our estate. I've created the following query, however, it will not work as a chart due to the fact it returns a single row:

Select Distinct Top 1000000  SUM(Case tblAssetCustom.Model
    When 'VMware Virtual Platform' Then 1 When 'Virtual Machine' Then 1 Else 0 End) As 'Virtual Servers',
	SUM(Case tblAssetCustom.Model
    When 'VMware Virtual Platform' Then 0 When 'Virtual Machine' Then 0 Else 1 End) As 'Physical Servers'
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1

Is anyone able to help me translate this into a chart friendly query?

Thanks,

Chris

[Hemoco · Accepted solution]

Something like this?

SELECT DISTINCT TOP 1000000 CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END
AS Server, COUNT(*) AS Total
FROM tblComputersystem INNER JOIN
tblAssets ON tblComputersystem.AssetID = tblAssets.AssetID INNER JOIN
tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
WHERE (tblComputersystem.Domainrole > 1) AND (tblAssetCustom.State = 1)
GROUP BY CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END

[cbtg2006]

Lansweeper wrote:
Something like this?

SELECT DISTINCT TOP 1000000 CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END
AS Server, COUNT(*) AS Total
FROM tblComputersystem INNER JOIN
tblAssets ON tblComputersystem.AssetID = tblAssets.AssetID INNER JOIN
tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
WHERE (tblComputersystem.Domainrole > 1) AND (tblAssetCustom.State = 1)
GROUP BY CASE tblAssetCustom.Model WHEN 'VMware Virtual Platform' THEN 'Virtual' WHEN 'Virtual Machine' THEN 'Virtual' ELSE 'Physical' END

Perfect, thank you!