that was spot on Richard. Thank you for your time and the solution. On Wed, Oct 5, 2011 at 3:22 PM, Richard Huxton <d...@archonet.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 >
