How to do this in one sql statement without CTE ?

SQLServerCentral » Latest topics

How to do this in one sql statement without CTE ?

I am able to query  Total number of Devices  from below CTE. 

[code language="sql"]
with Alarmreport (ID,SiteName,DEVICENAME)

more info...