Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    28

    Unanswered: SQL Assistance Needed

    The following SQL:

    Select endpoint_label, Name, Avg(MetricValue)
    From LM_MEM
    Where (((Name = 'Availbytes') or (Name = 'PagesInputPerSec')) and Stat = 'AVG')
    Group By endpoint_label, Name

    produces two lines per endpoint_label.

    I want to have one line per endpoint_label and I'm not successful in my attempts at this.
    I'm trying for an end result such as:
    endpoint_label, Availbytes, PagesInputPerSec
    xxx1234xxxxxx, 6.123456, 15.446223
    xxx1001xxxxxx, 6.673322, 15.977110

    When I'm able to list each endpoint_label once, the Avg(MetricValue) for the two Names is incorrect and it's repeated for every row.
    endpoint_label, Availbytes, PagesInputPerSec
    xxx1234xxxxxx, 1.123456, 1.34876
    xxx1001xxxxxx, 1.123456, 1.34876

    Any assistance on this would be greatly appreciated.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please post the table layout for LM_MEM
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Maybe this works:

    Code:
    with endpoint_temp (endpoint_label,Availbytes,PagesInputPerSec) as
              (select endpoint_label,
                      case when Name = 'Availbytes' then MetricValue
                                                    else 0
                      end as Availbytes,
                      case when Name = 'PagesInputPerSec' then MetricValue
                                                    else 0
                      end as PagesInputPerSec
                 from LM_MEM
                where NAME in ('Availbytes','PagesInputPerSec')
                  and Stat = 'AVG')
     select endpoint_label,
            sum(Availbytes)       as "Availbytes" ,
            sum(PagesInputPerSec) as "PagesInputPerSec"
       from endpoint_temp
     group by endpoint_label;

  4. #4
    Join Date
    Apr 2005
    Posts
    28
    This helps, but is not giving me the proper Avg(MetricValue) for each of the two measurements. I'm not sure what it's actually displaying.

    I'll play around with it and see if I can make it work.
    Thanks for the directiion.


    Quote Originally Posted by GertK
    Maybe this works:

    Code:
    with endpoint_temp (endpoint_label,Availbytes,PagesInputPerSec) as
              (select endpoint_label,
                      case when Name = 'Availbytes' then MetricValue
                                                    else 0
                      end as Availbytes,
                      case when Name = 'PagesInputPerSec' then MetricValue
                                                    else 0
                      end as PagesInputPerSec
                 from LM_MEM
                where NAME in ('Availbytes','PagesInputPerSec')
                  and Stat = 'AVG')
     select endpoint_label,
            sum(Availbytes)       as "Availbytes" ,
            sum(PagesInputPerSec) as "PagesInputPerSec"
       from endpoint_temp
     group by endpoint_label;

  5. #5
    Join Date
    Apr 2005
    Posts
    28
    I changed the "else 0" to "else null"
    This seems to give me the results I was looking for.

    Thanks!

    Quote Originally Posted by bunzo
    This helps, but is not giving me the proper Avg(MetricValue) for each of the two measurements. I'm not sure what it's actually displaying.

    I'll play around with it and see if I can make it work.
    Thanks for the directiion.

  6. #6
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Because you didn't supply the original DDL needed to recreate the table and didn't give some data samples my code was a bit of a guess. If you do that the next time the answer will most likely be more accurate.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •