Re: Analytic type functionality, matching patters in a column then increment an integer

that was spot on Richard. Thank you for your time and the solution.

On Wed, Oct 5, 2011 at 3:22 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:

> On 05/10/11 19:29, Henry Drexler wrote:
>
>>
>> and would like to have a column indicate like this:
>>
>> 'evaluation' 'indicator'
>> tf 1
>> tt 1
>> ft 1
>> ff
>> ff
>> tf 2
>> ft 2
>> tf 3
>> tt 3
>> ft 3
>> ff
>>
>
> SELECT id,evaluation,sum(case when evaluation='tf' then 1 else 0 end) over
> (order by id) FROM tfcount ORDER BY id;
>
> id | evaluation | sum
> ----+------------+-----
> 1 | tf | 1
> 2 | tt | 1
> 3 | ft | 1
> 4 | ff | 1
> 5 | ff | 1
> 6 | tf | 2
> 7 | ft | 2
> 8 | tf | 3
> 9 | tt | 3
> 10 | ft | 3
> 11 | ff | 3
> (11 rows)
>
> OK, so that's almost it, but you'd like "ff" to be null. You probably can
> do it with a suitably nested CASE, but it's probably clearer as a sub-query.
>
> SELECT
> id,
> evaluation,
> CASE WHEN evaluation='ff' THEN null::int
> ELSE sum::int END AS section_num
> FROM (
> SELECT
> id,
> evaluation,
> sum(case when evaluation='tf' then 1 else 0 end) over (order by id)
> FROM tfcount
> ) AS rows
> ORDER BY id;
>
> HTH
>
> P.S. - I always find the windowing function syntax confusing, but it's as
> the standards define I believe.
>
> --
> Richard Huxton
> Archonet Ltd
>