group by and aggregate functions on regular expressions

From: "Rhys Stewart" <rhys(dot)stewart(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: group by and aggregate functions on regular expressions
Date: 2007-03-08 18:33:40
Message-ID: 189966030703081033j1030171cncb88a4c2802320e5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ted Byers 2007-03-08 18:34:51 Re: OT: Canadian Tax Database
Previous Message Richard Broersma Jr 2007-03-08 18:25:56 Re: Database slowness -- my design, hardware, or both?