SQL query - help needed

[aiwaxxx]

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

[PAVB]

What do you have some far?

[sridhar.dbe]

hi,
try this one.
I think this is what you want.

select name,
max(decode( rn,1,cn)) as FastEthernet2,
max(decode( rn,2,cn)) as FastEthernet5,
max(decode( rn,3,cn)) as FastEthernet6
from (
select name,
row_number() over (partition by name
order by name) rn,
count(*) cn from a
group by name,substr(INTERFACES,1,instr(INTERFACES,'/')-1)
) group by name;


Output-----
------------------------------------------------

NAME FASTETHERNET2 FASTETHERNET5 FASTETHERNET6
-------------------- ------------- ------------- -------------
BE_1-192.168.123.100 3 5 4
BE_2-192.168.123.101 3 3

[aiwaxxx]

thnaks Sridhar..

i managed to solve it after a few changes to the data
(Find FastEthernet. Replace with Fa
and Find GigabitEthernet. Replace with Gi)

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

[PAVB]

I have to say this is a very interesting concept, kinda "reverse query troubleshooting" which if I get it right sounds like "if you can't get your query to work... change the data!"

I'm sure your business users would love the approach.

I'm wondering how it works with DBA's rule #37... "DBA do not owns the data, Business owns the data".

[sridhar.dbe]

According to my knowledge,
there is no Left in Oracle
R u working on Oracle or sql server.

thnaks Sridhar..

i managed to solve it after a few changes to the data
(Find FastEthernet. Replace with Fa
and Find GigabitEthernet. Replace with Gi)

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