Subject help for a query where i need percentage calculation ndirondello marco 2012-05-10T12:50:55Z
hi all, i have this table

TABLE DATA
(
ID bigint NOT NULL,
TIME timestamp NOT NULL,
SOURCE_ID bigint,
VALUE double precision NOT NULL,
);

i need to do a query or a procedure to group data by SOURCE_ID and to get

SOURCE_ID, Max(time), percentage of rows where value < 5, percentage of rows where value < 10

my problem is how calculate the last 2 fields

so if i have this data (timeX are timestamp)
1, "time0", 100, 3
2, "time1", 200, 7
3, "time2", 100, 11
4, "time3", 100, 9
5, "time4", 200, 14

i need percentage0 for source_id = 100
1 (# rows with value < 5 and source_id = 100) / 3 (# rows with source_id = 100) = 0.6
percentage1 for source_id = 100
2 (# rows with value < 10 and source_id = 100) / 3 (# rows with source_id = 100) = 0.3
percentage0 for source_id = 200
0 (# rows with value < 5 and source_id = 200) / 2 (# rows with source_id = 200) = 0
i need percentage0 for source_id = 100
1 (# rows with value < 10 and source_id = 200) / 2 (# rows with source_id = 200) = 0.5

so my query need to return

100, maxtime0, 0.6, 0.3
200, maxtime1, 0, 0.5

how can i do that query? i hope that you understand my example
my table have a lot of data (about 100 milions of record) so i need also that the query is fastest

thanks