SQL Assistance Needed

bunzo · #1 (**permalink**) 01-23-06, 17:10

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.

Marcus_A · #2 (**permalink**) 01-23-06, 17:31

Please post the table layout for LM_MEM

GertK · #3 (**permalink**) 01-23-06, 21:28

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;

bunzo · #4 (**permalink**) 01-24-06, 13:50

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;

bunzo · #5 (**permalink**) 01-24-06, 13:57

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.

GertK · #6 (**permalink**) 01-24-06, 14:11

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech