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