I need to query numbers of phones per site for Analog, and IP phones separately. I can do it with these two queries.
IP Phones:
select count(d.name) as IP_Phones, dp.name as DevicePool
from Device as d
inner join DevicePool as dp on d.fkDevicePool=dp.pkid
inner join typemodel as tm on tm.enum=d.tkmodel
where (tm.name != 'Analog Phone' and tm.name != 'Conference Bridge'
and tm.name != 'CTI Route Point' and tm.name != 'CTI Port'
and tm.name != 'MGCP Station' and tm.name != 'Route List'
and tm.name != 'H.323 Gateway'
and tm.name != 'Music On Hold'
and tm.name != 'Media Termination Point'
and tm.name != 'Tone Announcement Player'
and tm.name != 'Cisco IOS Conference Bridge (HDV2)'
and tm.name != 'Cisco IOS Software Media Termination Point (HDV2)'
and tm.name != 'Cisco IOS Media Termination Point (HDV2)'
and tm.name != 'SIP Trunk' and dp.name like '%PH%')
group by dp.name
order by dp.name
which results in
ip_phones devicepool
========= ================
815 Site1-DP
43 Site2-DP
32 Site3-DP
890 Site4-DP
Analog Phones:
select count(d.name) as Analog_Phones, dp.name as DevicePool
from Device as d
inner join DevicePool as dp on d.fkDevicePool=dp.pkid
inner join typemodel as tm on tm.enum=d.tkmodel
where (tm.name = 'Analog Phone' and dp.name like '%PH%')
group by dp.name
order by dp.name
which results in
analog_phones devicepool
============= ==============
12 Site1-DP
14 Site2-DP
1 Site3-DP
4 Site4-DP
What I'm looking for is a single query that results in something like this:
ip_phones analog_phones devicepool
========= ============= ==========
815 12 Site1-DP
43 14 Site2-DP
32 1 Site3-DP
890 4 Site4-DP