Re: group by and aggregate functions on regular expressions

select count(*), address where address ~* 'magil' or address ~*
'whitewater' etc group by address

would that work?

Rhys Stewart wrote:
> Hi all,
> i have a table with an address column. I wanted to count the number of
> rows with a given regex match. so i ended up with the following very
> verbose query:
>
>
> select
> address ~* 'magil' as Magil ,
> address ~* 'whitewater' as whitewater,
> (address ~* 'inswood' or address ~* 'innswood') as innswood,
> (address ~* 'eltham' AND address ~* 'view') as eltham_view,
> (address ~* 'eltham' AND address ~* 'acre') as eltham_acres,
> (address ~* 'eltham' AND address ~* 'vista') as eltham_vista,
> count(prem)
>
> from prem_info
> where
> address ~* 'magil'
> or (address ~* 'eltham' AND address ~* 'view')
> or (address ~* 'eltham' AND address ~* 'acre')
> or (address ~* 'eltham' AND address ~* 'vista')
> or address ~* 'whitewater'
> or (address ~* 'inswood' or address ~* 'innswood')
> and parish = 'SpanishTown'
> group by Magil, whitewater, innswood, eltham_view,
> eltham_acres,eltham_vista
>
> and i got this:
>
> magil whitewater innswood eltham_view eltham_acres
> eltham_vista count
> f t f f f f 650
> t f f f f f 361
> f f f f f t 181
> f f f f t f 462
> f f f t f f 542
> f f t f f f 686
>
>
> useful but not in the format that would be nice. so the question:
> is there any way to rewrite this query or are there any existing
> functions that would give me a tabular output like so:
>
> community count
> magil 361
> whitewater 650
> inswood 686
> eltham_view 542
>
> etc..
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly