I normally use subselects.
e.g. SELECT SOURCE_ID,Max(time), (select count(*) from DATA as gld where
gld.SOURCE_ID= DATA.SOURCE_ID AND gld.value < 5) as countless5
FROM DATA GROUP BY SOURCE_ID

Make sure there is an index on SOURCE_ID.

Depending on the data and hardware doing it via a select only might be too
slow for you. A stored procedure to fill a summary table might be faster.
And using triggers to keep summary table up to date.

On Thu, May 10, 2012 at 2:50 PM, ndirondello marco <korkless@...>wrote:

> **
>
>
> 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
>
>
>


[Non-text portions of this message have been removed]