Hi,
I'm trying to summarize some data which is stored as rows/columns and name/value pairs using standard postgresql capabilities (not special functions).

I think a case statement should work but am not sure my logic is sound.

The rows are as follows:

Timestamp, Element, Type, Value
==========================

The 'Type' can be one of several strings but I am really interested only in 'CallsInProgress' and 'BandwidthAvailable'. The 'Element' is the thing that has these properties and there can be several. The timestamp is down to a second.

The output I'm looking for is the peak CallsInProgress and min BandwidthAvailable (they should correspond to one another) every hour grouped by unique Element.

Hour, Element, PeakCallsInProgress, MinBandwidthAvailable

The approach I took was to first try and sort out the data using the case statement and then the max/min (probably doesn't matter) to reduce the extra rows and collapse the set; that corresponds to the inner sql query. If I don't do that, I'll have something like

elementname, CallsInProgress, BandwidthAvailable, Hour
--------------------------------------------------------
EL1, 52, null , 2011-5-31 12:00
EL1, null , 12 , 2011-5-31 12:00

The max/min converts it into (again I think I could use either max or min to collapse them actually)

elementname, CallsInProgress, BandwidthAvailable, Hour
--------------------------------------------------------
EL1, 52, 12, 2011-5-31 12:00
EL1, 51, 13, 2011-5-31 12:00

Now, I still will have multiple rows with the same 'Hour' and different values which is why I need the outer 'group by' and 'max(temp.peakcallsinprogress)'. I'm assuming by just focusing on the max peakcallsinprogress, I can return the accompanying bandwidthavailable which will be a min.

Does this sound reasonable? Thanks in advance.

select temp.elementname, max(temp.peakcallsinprogress) as peakcallsinprogress, temp.minbwavailable, temp.hour from
(select sepia.elementname as elementname,
max(case when seca.type = 'CallsInProgress' then seca.value end) as peakcallsinprogress,
min(case when seca.type = 'BandwidthAvailable' then seca.value end) as minbwavailable,
date_trunc('hour', seca.sync_timestamp) as hour
from default_system_element_counter_audit seca
left join default_system_element_performance_info_audit sepia on sepia.audit_id = seca.systemperformanceproperty_id
where seca.sync_timestamp >= (NOW() - interval '24 day')
and sepia.elementtype = 'ELEMENT_LOCATION'
group by sepia.elementname, seca.sync_timestamp ) as temp
group by temp.elementname, temp.hour, temp.minbwavailable
order by temp.hour, temp.elementname

I should clarify that the problem I am seeing is the last or outer 'max(temp.peakcallsinprogress)' is not having the desired effect. I'm still seeing multiple rows returned for the same elementname, hour.

elementname, CallsInProgress, BandwidthAvailable, Hour
--------------------------------------------------------
EL1, 52, 12, 2011-5-31 12:00
EL1, 51, 13, 2011-5-31 12:00

when I would expect just
elementname, CallsInProgress, BandwidthAvailable, Hour
--------------------------------------------------------
EL1, 52, 12, 2011-5-31 12:00