Skip site navigation (1) Skip section navigation (2)

Re: how to use aggregate functions in this case

From: David Johnston <polobo(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: how to use aggregate functions in this case
Date: 2013-08-26 13:59:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
BladeOfLight16 wrote
> Then again, I guess you don't need a nested query.
> SELECT  v_rec1.user,
>         CASE WIDTH_BUCKET(v_rec_fts.lev, 0, 100, 4)
>               WHEN 1 THEN '0 to 25'
>               WHEN 2 THEN '25 to 50'
>               WHEN 3 THEN '50 to 75'
>               WHEN 4 THEN '75 to 100'
>               ELSE 'But how?'
>         END CASE AS quarter_percentage
>          COUNT(*) as count,
> FROM v_rec2
> GROUP BY user, quarter_percentage;

This is clean but requires the use of equal intervals.

Another option, though I am unfamiliar with the exact syntax, is to use the
contains operator and an "intrange" (range type, for integer or whatever
actual type is needed).

SELECT CASE WHEN val @> '(0, 25]' -- '(25,50]', etc...

This allows for uneven bucket sizes and avoid the double-inclusive endpoints
problem that results from using BETWEEN.

Requires 9.2

David J.

View this message in context:
Sent from the PostgreSQL - general mailing list archive at

In response to


pgsql-general by date

Next:From: Mistina MichalDate: 2013-08-26 14:02:30
Subject: Re: Re: still exists after pacemaker stopped postgresql - how to remove
Previous:From: Samrat RevagadeDate: 2013-08-26 13:32:58
Subject: Re: effective_io_concurrency with an SSD SAN?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group