SQL Query - help needed

aiwaxxx

Guppy
experts,

i need some help in writing a query.

i have the below table. i need to count the number of INTERFACES starting with FastEthernet2,FastEthernet5 and so on and group them by the NAME column.

NAME INTERFACES
BE_1-192.168.123.100 FastEthernet2/11
BE_1-192.168.123.100 FastEthernet2/13
BE_1-192.168.123.100 FastEthernet2/15
BE_1-192.168.123.100 FastEthernet5/10
BE_1-192.168.123.100 FastEthernet5/12
BE_1-192.168.123.100 FastEthernet5/14
BE_1-192.168.123.100 FastEthernet5/15
BE_1-192.168.123.100 FastEthernet5/19
BE_1-192.168.123.100 FastEthernet6/25
BE_1-192.168.123.100 FastEthernet6/26
BE_1-192.168.123.100 FastEthernet6/27
BE_1-192.168.123.100 FastEthernet6/28
BE_2-192.168.123.101 FastEthernet2/2
BE_2-192.168.123.101 FastEthernet2/7
BE_2-192.168.123.101 FastEthernet2/10
BE_2-192.168.123.101 FastEthernet5/10
BE_2-192.168.123.101 FastEthernet5/12
BE_2-192.168.123.101 FastEthernet5/14

the output should be something like this-

NAME Fasthethernet2 Fasthethernet5 Fasthethernet6
BE_1-192.168.123.100 3 5 4

BE_2-192.168.123.101 X Y Z
 
Hi

try that if you are using MSSQL

SELECT name, SUBSTRING(interface, 1, 13) AS interface, COUNT(SUBSTRING(interface, 1, 13)) AS aCount
FROM ***NAME of the TABLE***
GROUP BY name, SUBSTRING(interface, 1, 13)
ORDER BY name, aCount


this will return you

BE_1-192.168.123.100 FastEthernet2 3
BE_1-192.168.123.100 FastEthernet6 4
BE_1-192.168.123.100 FastEthernet5 5
BE_2-192.168.123.101 FastEthernet5 3
BE_2-192.168.123.101 FastEthernet2 3


Cheers
Vita
 
got it working ..thanks....small modification needed as my interface column had some other entires as well.

i'm using MS acccess..

SELECT F2, LEFT(F3,4) AS interface, COUNT(LEFT(F3,4)) AS aCount FROM
Table1
where F8<>"up / up" GROUP BY F2, LEFT(F3,4) ORDER BY F2, aCount

thanks again.
 
Back
Top