SELECT regiondefinition.regionname as "Region",aaov.NAME AS "Site",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left join componentdefinitionlaptop on componentdefinitionlaptop.componentid=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Workstation' and componentdefinitionlaptop.islaptop is not null) as "Workstation-Laptop Count",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left join componentdefinitionlaptop on componentdefinitionlaptop.componentid=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Workstation' and  componentdefinitionlaptop.islaptop is null) as "Workstation-Desktop Count",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Server') as "Server",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Access Point') as "Access Point",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Firewall') as "Firewall",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Printer') as "Printer",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Projector') as "Projector",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Rack') as "Rack",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Router') as "Router",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Scanner') as "Scanner",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Storage Device') as "Storage Device",
(select count(*) from resources left JOIN ComponentDefinition product ON resources.COMPONENTID=product.COMPONENTID
left JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID  left JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
where resLocation.SITEID=siteDef.SITEID and productType.componenttypename='Switch') as "Switch" from SiteDefinition siteDef
left JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID left join regiondefinition on regiondefinition.regionid=siteDef.regionid group by regiondefinition.regionname,aaov.NAME,siteDef.SITEID