SQL query - help needed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL query - help needed

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Jan 2009
    Posts
    11

    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

  4. #4
    Join Date
    Feb 2009
    Posts
    2
    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  6. #6
    Join Date
    Jan 2009
    Posts
    11
    According to my knowledge,
    there is no Left in Oracle
    R u working on Oracle or sql server.
    Quote Originally Posted by aiwaxxx View Post
    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
  •  


Click Here to Expand Forum to Full Width