-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
sridhar
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".
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
According to my knowledge,
there is no Left in Oracle
R u working on Oracle or sql server.
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|