Average of result set row

Question

I have a table of bandwidth utilization data. Each row has a hostname,niccardname, utilization percentage and timestamp. At maximum timestamp for each host there can be different NIC cards. So for Each host i want average % utilization of different NIC cards at maximum timestamp.

Below is my table structure , insertions and queries -

CREATE TABLE bandwith_utilization
(
  id integer NOT NULL,
  hostname character varying(255),
  "timestamp" bigint,
  niccardname character varying(255),
  percentageutilization integer,
  CONSTRAINT bandwidth_utilization_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bandwith_utilization
  OWNER TO postgres;


INSERT INTO bandwith_utilization
VALUES (1,'host1','111111','nic1',40);
INSERT INTO bandwith_utilization
VALUES (2,'host1','111112','nic1',50);
INSERT INTO bandwith_utilization
VALUES (3,'host1','111113','nic1',50);
INSERT INTO bandwith_utilization
VALUES (4,'host1','111113','nic2',70);

INSERT INTO bandwith_utilization
VALUES (5,'host2','111111','nic1',80);
INSERT INTO bandwith_utilization
VALUES (6,'host2','111112','nic1',20);
INSERT INTO bandwith_utilization
VALUES (7,'host2','111112','nic2',30);

INSERT INTO bandwith_utilization
VALUES (8,'host3','111115','nic1',10);

So this is my table after insertions -

id  hostname    timestamp   niccardname     percentageutilization
------------------------------------------------------------------
1;  "host1";    111111;     "nic1";         40
2;  "host1";    111112;     "nic1";         50
3;  "host1";    111113;     "nic1";         50
4;  "host1";    111113;     "nic2";         70

5;  "host2";    111111;     "nic1";         80
6;  "host2";    111112;     "nic1";         20
7;  "host2";    111112;     "nic2";         30

8;  "host3";    111115;     "nic1";         10

I have a query which gives my output for hostnames at max timestamp -

select hostname, timestamp, niccardname, percentageutilization
from report.bandwith_utilization
 where timestamp = (select max(timestamp)
                    from report.bandwith_utilization nwUtil
                    where nwUtil.hostname = report.bandwith_utilization.hostname
                   ) ;  

output of above query is -

"host1";  111113; "nic1"; 50
"host1";  111113; "nic2"; 70

"host2";  111112; "nic1"; 20
"host2";  111112; "nic2"; 30

"host3";  111115; "nic1"; 10

So Now My Expected out put is average % utilization of different NIC cards for each host. i.e.

"host1";  111113; "nic1"; 60
"host2";  111112; "nic1"; 25
"host3";  111115; "nic1"; 10

How can i find final average output with in the same query which i mentioned above?

Answer 1

should be an AVG() and group by

select hostname,timestamp,min(niccardname), avg(percentageutilization )
from report.bandwith_utilization
where (timestamp,hostname, niccardname)   in (select max(timestamp) ,hostname, niccardname
from report.bandwith_utilization nwUtil 
where nwUtil.hostname= report.bandwith_utilization.hostname
group by  hostname, niccardname
) 
group by  hostname,timestamp
order by  hostname,timestamp