| 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: | Whole Thread | Raw Message | 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? |