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:
magilwhitewaterinnswoodeltham_vieweltham_acres
eltham_vistacount
ftffff650
tfffff361
ffffft181
fffftf462
ffftff542
fftfff686
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:
communitycount
magil361
whitewater650
inswood686
eltham_view 542
etc..
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
