Обсуждение: group by and aggregate functions on regular expressions

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

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@postgresql.org so that your
>       message can get through to the mailing list cleanly