If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: SQL Assistance Needed

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

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;

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.

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;

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.