SQL query - help needed

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

What do you have some far?

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

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

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".

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

Quote:

---

Originally Posted by 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

---