Stumbled doing count

1

Hi All
I have stumbled to do a simple count in sql
what I would like to do is to do a count per AD site with the status of each system that is listed in columns
currently this displays the info per AD site per status below each other
this is what I get
ad_site_name0 laststatename (No column name)
siteA Applicable 66
siteA Installed 47
SiteB Applicable 27
SiteB Installed 25
SiteC Applicable 84
SiteC Installed 116
SiteD Applicable 24
SiteD Installed 23
SiteE Applicable 1270
SiteE Installed 1716
SiteF Applicable 282
SiteF Installed 392

What I would like to see - sorry formatting got a bit lost :frowning:

ad_site_name0 applicable installed percentage
SiteA 66 47 41.59
SiteB 27 25 48.07
SiteC 84 116 58
SiteD 24 23 48.93
SiteE 1270 1716 57.46
SiteF 282 392 58.16

select OS.Operating_System_Name_and0, COUNT(*) as 'Client count', inst.SMS_Installed_Sites0
from v_R_System os
inner join v_RA_System_SMSInstalledSites inst on os.resourceid=inst.resourceid
where (os.Client_Type0 = 1 and os.Decommissioned0 = 0) and (os.Obsolete0 = 0 and OS.Operating_System_Name_and0 like '%Workstation%')
GROUP BY Operating_System_Name_and0, SMS_Installed_Sites0

2

Ok cool
I figured it out
thx
select distinct

zs.ad_site_name0,
count (case zs.laststatename when 'Applicable' then 'Applicable' end) as 'Applicable'
,count (case zs.laststatename when 'installed' then 'Installed' end) as 'installed'

--count (distinct zs.netbios_name0)
from
z_Patch_Compliance zs
where zs.laststatename is not null
group by
zs.ad_site_name0
--, zs.laststatename
order by zs.AD_SITE_NAME0