select 
    tstamp::text,
    hmap(hostname),
    sum(l1count) as l1total,
    100-pct(sum(l1success),sum(l1count)) as l1failspct,
    avg0(sum(l1resp),sum(l1success)) as l1avg,
    avg0(sum(l2resp),sum(l2success)) as l2avg,
    avg0(sum(l3resp),sum(l3success)) as l3avg,
    (avg0(sum(l1resp),sum(l1success)) - avg0(sum(l2resp),(sum(l2success)))) as l12avgdiff,
    (avg0(sum(l2resp),sum(l2success)) - avg0(sum(l3resp),(sum(l3success)))) as l23avgdiff
 from (
    select 
        date_trunc('minute',brsa.lastflushtime) as tstamp,
        brsa.hostname, 
        brsa.businessrequestsummaryid as l1id,
        brsb.businessrequestsummaryid as l2id,
        brsc.businessrequestsummaryid as l3id,
        brsa.category||','||brsa.perspective||','||brsa.request as l1req,
        brsb.category||','||brsb.perspective||','||brsb.request as l2req,
        brsc.category||','||brsc.perspective||','||brsc.request as l3req,
        brsa.totalresponsetime as l1resp,
        brsa.totalcount as l1count,
        brsa.successcount as l1success,
        brsb.successcount as l2success,
        brsa.highestresponsetime as l1max,
        brsb.totalresponsetime as l2resp,
        brsb.totalcount as l2count,
        brsb.highestresponsetime as l2max,
        brsc.totalresponsetime as l3resp,
        brsc.totalcount as l3count,
        brsc.highestresponsetime as l3max,
        brsc.successcount as l3success
    from businessrequestsummary brsa 
    left join businessrequestsummary brsb 
    on (brsa.businessrequestsummaryid=brsb.primaryrequestid) 
    left join businessrequestsummary brsc 
    on (brsb.businessrequestsummaryid=brsc.primaryrequestid) 
    where brsa.category='Webservice' and
    brsa.lastflushtime between '2007-11-27 13:15:03' and '2007-11-27 13:45:03'
    and brsa.request='lowFareSearch' and
    brsa.perspective = 'LowFareSearchReq' -- and
    ) as a
group by a.tstamp , hostname
union all
select 
    'Rollup',
    hmap(hostname),
    sum(l1count) as l1total,
    100-pct(sum(l1success),sum(l1count)) as l1failspct,
    avg0(sum(l1resp),sum(l1success)) as l1avg,
    avg0(sum(l2resp),sum(l2success)) as l2avg,
    avg0(sum(l3resp),sum(l3success)) as l3avg,
    (avg0(sum(l1resp),sum(l1success)) - avg0(sum(l2resp),(sum(l2success)))) as l12avgdiff,
    (avg0(sum(l2resp),sum(l2success)) - avg0(sum(l3resp),(sum(l3success)))) as l23avgdiff
 from (
    select 
        date_trunc('min',brsa.lastflushtime) as tstamp,
        brsa.hostname, 
        brsa.businessrequestsummaryid as l1id,
        brsb.businessrequestsummaryid as l2id,
        brsc.businessrequestsummaryid as l3id,
        brsa.category||','||brsa.perspective||','||brsa.request as l1req,
        brsb.category||','||brsb.perspective||','||brsb.request as l2req,
        brsc.category||','||brsc.perspective||','||brsc.request as l3req,
        brsa.totalresponsetime as l1resp,
        brsa.totalcount as l1count,
        brsa.successcount as l1success,
        brsb.successcount as l2success,
        brsa.highestresponsetime as l1max,
        brsb.totalresponsetime as l2resp,
        brsb.totalcount as l2count,
        brsb.highestresponsetime as l2max,
        brsc.totalresponsetime as l3resp,
        brsc.totalcount as l3count,
        brsc.highestresponsetime as l3max,
        brsc.successcount as l3success
    from businessrequestsummary brsa 
    left join businessrequestsummary brsb 
    on (brsa.businessrequestsummaryid=brsb.primaryrequestid) 
    left join businessrequestsummary brsc 
    on (brsb.businessrequestsummaryid=brsc.primaryrequestid) 
    where brsa.category='Webservice' and
    brsa.lastflushtime between '2007-11-27 13:15:03' and '2007-11-27 13:45:03'
    and brsa.request='lowFareSearch' and
    brsa.perspective = 'LowFareSearchReq' -- and
    ) as b
    group by b.hostname
union all
select 
    'Std Dev:',
    hmap(hostname),
    stddev(l1total)::numeric(12,2) as l1totstddev,
    stddev(l1failspct)::numeric(12,2) as l1failspctstddev,
    stddev(l1avg)::numeric(12,2) as l1stddev,
    stddev(l2avg)::numeric(12,2) as l2stddev,
    stddev(l3avg)::numeric(12,2) as l3stddev,
    stddev(l12avgdiff)::numeric(12,2) as l12avgstddev,
    stddev(l23avgdiff)::numeric(12,2) as l23avgstddev
from (
    select 
--        tstamp::text,
        hmap(c.hostname) as hostname,
        sum(l1count) as l1total,
        100-pct(sum(l1success),sum(l1count)) as l1failspct,
        avg0(sum(l1resp),sum(l1success)) as l1avg,
        avg0(sum(l2resp),sum(l2success)) as l2avg,
        avg0(sum(l3resp),sum(l3success)) as l3avg,
        (avg0(sum(l1resp),sum(l1success)) - avg0(sum(l2resp),(sum(l2success)))) as l12avgdiff,
        (avg0(sum(l2resp),sum(l2success)) - avg0(sum(l3resp),(sum(l3success)))) as l23avgdiff
    from (
        select 
            date_trunc('minute',brsa.lastflushtime) as tstamp,
            brsa.hostname, 
            brsa.businessrequestsummaryid as l1id,
            brsb.businessrequestsummaryid as l2id,
            brsc.businessrequestsummaryid as l3id,
            brsa.category||','||brsa.perspective||','||brsa.request as l1req,
            brsb.category||','||brsb.perspective||','||brsb.request as l2req,
            brsc.category||','||brsc.perspective||','||brsc.request as l3req,
            brsa.totalresponsetime as l1resp,
            brsa.totalcount as l1count,
            brsa.successcount as l1success,
            brsb.successcount as l2success,
            brsa.highestresponsetime as l1max,
            brsb.totalresponsetime as l2resp,
            brsb.totalcount as l2count,
            brsb.highestresponsetime as l2max,
            brsc.totalresponsetime as l3resp,
            brsc.totalcount as l3count,
            brsc.highestresponsetime as l3max,
            brsc.successcount as l3success
        from businessrequestsummary brsa 
        left join businessrequestsummary brsb 
        on (brsa.businessrequestsummaryid=brsb.primaryrequestid) 
        left join businessrequestsummary brsc 
        on (brsb.businessrequestsummaryid=brsc.primaryrequestid) 
        where brsa.category='Webservice' and
        brsa.lastflushtime between '2007-11-27 09:14' and '2007-11-27 09:25:40'
        and brsa.request='lowFareSearch' and
        brsa.perspective = 'LowFareSearchReq' -- and
        ) as c
    group by c.tstamp, hostname
    ) as d
group by hostname
order by 2, tstamp asc, l1total