I have 2 queries. 1 shows me the SCCM clients installed in an AD Site [With SCCM clients] and the other shows the clients assigned to the AD Site [total with and without SCCM clients]

I would like to merge them to a single sql report - So I see

AD Site  | Clients Assigned to AD Site | SCCM Client installed in that AD Site

Here is query to find SCCM clients installed in that AD Site

========================================

Select    AD_Site_Name0 as 'AD Site Name',count(DISTINCT name0) as 'Clients'
FROM v_R_System where Active0 = 1 and Client0 =1 and Obsolete0 != 1 and ad_site_name0 != 'null'
group by AD_Site_Name0
Union all
Select 'TOTAL', sum(A.Clients) from (Select    AD_Site_Name0 as 'AD Site Name',count(DISTINCT name0) as 'Clients'
FROM v_R_System where Active0 = 1 and Client0 =1 and Obsolete0 != 1 and ad_site_name0 != 'null'
group by AD_Site_Name0)A


Here is query to find clients assigned in that AD Site
======================================================
Select    AD_Site_Name0 as 'AD Site Name',count(DISTINCT name0) as 'Clients'
FROM v_R_System where ad_site_name0 != 'null'

group by AD_Site_Name0
Union all
Select 'TOTAL', sum(A.Clients) from (Select    AD_Site_Name0 as 'AD Site Name',count(DISTINCT name0) as 'Clients'
FROM v_R_System where ad_site_name0 != 'null'
group by AD_Site_Name0)A

So what exactly is the error or issue you are getting? Did you write these queries?

Hello,

Not entierly sure what you are looking for here. But something like this?

SELECT DISTINCT sys.AD_Site_Name0
,COUNT(sys.AD_Site_Name0) AS 'ObjectsInADSite'
,COUNT(cli.ResourceID) AS 'InstalledClients'
FROM v_R_System AS sys
LEFT OUTER JOIN (
SELECT DISTINCT ResourceID
FROM v_R_System
WHERE Obsolete0 = 0
AND Active0 = 1
AND Client0 = 1) AS cli
ON cli.ResourceID = sys.ResourceID
GROUP BY sys.AD_Site_Name0

• Marked as answer by Vik Singh 3 hours 50 minutes ago

Hello,

Not entierly sure what you are looking for here. But something like this?

SELECT DISTINCT sys.AD_Site_Name0
,COUNT(sys.AD_Site_Name0) AS 'ObjectsInADSite'
,COUNT(cli.ResourceID) AS 'InstalledClients'
FROM v_R_System AS sys
LEFT OUTER JOIN (
SELECT DISTINCT ResourceID
FROM v_R_System
WHERE Obsolete0 = 0
AND Active0 = 1
AND Client0 = 1) AS cli
ON cli.ResourceID = sys.ResourceID
GROUP BY sys.AD_Site_Name0

• Marked as answer by Vik Singh Wednesday, April 22, 2015 3:56 AM

Thanks David. This is what I needed.

I would like to get a total in the end as I used to use previously if possible.

Also, though I have mentioned AD_Site_Name0 != 'ABC' - this adsite still shows up. I want to exclude this one.

SELECT DISTINCT sys.AD_Site_Name0
,COUNT(sys.AD_Site_Name0) AS 'ObjectsInADSite'
,COUNT(cli.ResourceID) AS 'InstalledClients'
 FROM v_R_System AS sys
 LEFT OUTER JOIN (
SELECT DISTINCT ResourceID
FROM v_R_System
WHERE Obsolete0 = 0 and AD_Site_Name0 != 'ABC'
AND Active0 = 1
AND Client0 = 1) AS cli
ON cli.ResourceID = sys.ResourceID
 GROUP BY sys.AD_Site_Name0

Okay,

I am definitely no SQL expert. And I am sure this could be done in a better way. But, using the logic you already had in place:

SELECT sys.AD_Site_Name0
	,COUNT(sys.ResourceID) AS 'ObjectsInADSite'
	,COUNT(cli.ResourceID) AS 'InstalledClients'
FROM v_R_System AS sys
LEFT OUTER JOIN (
	SELECT ResourceID
	FROM v_R_System
	WHERE Obsolete0 = 0
		AND Active0 = 1
		AND Client0 = 1) AS cli
	ON cli.ResourceID = sys.ResourceID
WHERE sys.AD_Site_Name0 != 'ABC'
GROUP BY sys.AD_Site_Name0

UNION ALL

SELECT 'TOTAL'
	,SUM(sq.ObjectsInADSite)
	,SUM(sq.InstalledClients)
FROM (
	SELECT sys.AD_Site_Name0
		,COUNT(sys.ResourceID) AS 'ObjectsInADSite'
		,COUNT(cli.ResourceID) AS 'InstalledClients'
	FROM v_R_System AS sys
	LEFT OUTER JOIN (
		SELECT ResourceID
		FROM v_R_System
		WHERE Obsolete0 = 0
			AND Active0 = 1
			AND Client0 = 1) AS cli
		ON cli.ResourceID = sys.ResourceID
	WHERE sys.AD_Site_Name0 != 'ABC'
	GROUP BY sys.AD_Site_Name0) AS sq

Okay,

I am definitely no SQL expert. And I am sure this could be done in a better way. But, using the logic you already had in place:

SELECT sys.AD_Site_Name0
	,COUNT(sys.ResourceID) AS 'ObjectsInADSite'
	,COUNT(cli.ResourceID) AS 'InstalledClients'
FROM v_R_System AS sys
LEFT OUTER JOIN (
	SELECT ResourceID
	FROM v_R_System
	WHERE Obsolete0 = 0
		AND Active0 = 1
		AND Client0 = 1) AS cli
	ON cli.ResourceID = sys.ResourceID
WHERE sys.AD_Site_Name0 != 'ABC'
GROUP BY sys.AD_Site_Name0

UNION ALL

SELECT 'TOTAL'
	,SUM(sq.ObjectsInADSite)
	,SUM(sq.InstalledClients)
FROM (
	SELECT sys.AD_Site_Name0
		,COUNT(sys.ResourceID) AS 'ObjectsInADSite'
		,COUNT(cli.ResourceID) AS 'InstalledClients'
	FROM v_R_System AS sys
	LEFT OUTER JOIN (
		SELECT ResourceID
		FROM v_R_System
		WHERE Obsolete0 = 0
			AND Active0 = 1
			AND Client0 = 1) AS cli
		ON cli.ResourceID = sys.ResourceID
	WHERE sys.AD_Site_Name0 != 'ABC'
	GROUP BY sys.AD_Site_Name0) AS sq

• Marked as answer by Vik Singh 4 hours 1 minutes ago

Okay,

I am definitely no SQL expert. And I am sure this could be done in a better way. But, using the logic you already had in place:

SELECT sys.AD_Site_Name0
	,COUNT(sys.ResourceID) AS 'ObjectsInADSite'
	,COUNT(cli.ResourceID) AS 'InstalledClients'
FROM v_R_System AS sys
LEFT OUTER JOIN (
	SELECT ResourceID
	FROM v_R_System
	WHERE Obsolete0 = 0
		AND Active0 = 1
		AND Client0 = 1) AS cli
	ON cli.ResourceID = sys.ResourceID
WHERE sys.AD_Site_Name0 != 'ABC'
GROUP BY sys.AD_Site_Name0

UNION ALL

SELECT 'TOTAL'
	,SUM(sq.ObjectsInADSite)
	,SUM(sq.InstalledClients)
FROM (
	SELECT sys.AD_Site_Name0
		,COUNT(sys.ResourceID) AS 'ObjectsInADSite'
		,COUNT(cli.ResourceID) AS 'InstalledClients'
	FROM v_R_System AS sys
	LEFT OUTER JOIN (
		SELECT ResourceID
		FROM v_R_System
		WHERE Obsolete0 = 0
			AND Active0 = 1
			AND Client0 = 1) AS cli
		ON cli.ResourceID = sys.ResourceID
	WHERE sys.AD_Site_Name0 != 'ABC'
	GROUP BY sys.AD_Site_Name0) AS sq

• Marked as answer by Vik Singh Wednesday, April 29, 2015 3:43 AM

Thanks!!