如何编写此sql查询？结果有相同的值，我想看看下面

Hotspot_Adi | Referans_Numarasi | Il |  Bolge | CA     | ADETI |   KYS    | ADET
A           | xxxx              | y  |  z     | AP     | 2     |   AP     |    2
A           | xxxx              | y  |  z     | Modem  | 1     |   Modem  |    1

但我明白了

Hotspot_Adi | Referans_Numarasi | Il |  Bolge | CA     | ADETI |   KYS    | ADET
A           | xxxx              | y  |  z     | AP     | 2     |   AP     |    2
A           | xxxx              | y  |  z     | Modem  | 1     |   Modem  |    1
A           | xxxx              | y  |  z     | Modem  | 1     |   AP     |    2
A           | xxxx              | y  |  z     | AP     | 2     |   Modem  |    1

我的问题是；

SELECT a.Hotspot_Adi, a.Referans_Numarasi, a.Il, a.Bolge, a.CA, a.ADETI, b.KYS, b.ADET 
FROM 
( 
 SELECT t1.Hotspot_Adi, t1.Referans_Numarasi, st.symbol AS Il ,
        Bol.sym AS Bolge, t1.Envanter_Sinif AS CA, 
        Count(t1.Envanter_Sinif) AS ADETI
 FROM  mdb.dbo.zODYA_EnvanterBilgi AS t1
 INNER JOIN mdb.dbo.ca_location ON t1.Referans_Numarasi =mdb.dbo.ca_location.zReferansNo          
 INNER JOIN mdb.dbo.ca_state_province AS st ON mdb.dbo.ca_location.state = st.id 
 INNER JOIN mdb.dbo.usp_contact AS t3 ON mdb.dbo.ca_location.zReferansNo = t3.zReferansNo 
 INNER JOIN mdb.dbo.zODYA_BolgeMudurlukleri AS Bol ON t3.zODYA_BolgeMudurlukleriID = Bol.id 
 WHERE (t1.Envanter_Sinif like '%Access%' 
        or 
        t1.Envanter_Sinif like '%Modem%'
        or t1.Envanter_Sinif like '%Switch%'
       )
GROUP BY t1.Hotspot_Adi, t1.Envanter_Sinif, t1.Referans_Numarasi, st.symbol, Bol.sym
)AS a
INNER JOIN
(
 SELECT t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif AS KYS, 
        Count(t5.Envanter_Sinif) AS ADET
 FROM mdb.dbo.zODYA_EnvanterBilgi AS t5
 INNER JOIN NetPerfMon.dbo.Nodes AS t2 ON t5.Seri_Numarasi = t2.Serial_Number
 WHERE (t5.Envanter_Sinif like '%Access%' 
        or 
        t5.Envanter_Sinif like '%Modem%' 
        or t5.Envanter_Sinif like '%Switch%'
        )
 GROUP BY t5.Hotspot_Adi, t5.Referans_Numarasi, t5.Envanter_Sinif
) AS b
ON a.Referans_Numarasi = b.Referans_Numarasi

我该怎么办？谢谢您。