Use a CASE statement? something like: select case WHEN os ~* E'^windows' then 'windows' WHEN os ~* E'server' then 'server' WHEN os ~* E'nix$' then '*nix' else 'other' end as osval, count(*) from os_tbl group by osval order by osval; The hard part is making sure your regexes cover all the bases, without duplication. It still sounds like the value should be a reference to a unique value in a small table of operating system entries, then store the value, rather than the string, in the main table. Susan -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of jackassplus Sent: Wednesday, September 08, 2010 12:22 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: Re: how do i count() similar items <snip> > To ensure data integrity, > you should probably create a fruit_type table with a unique column that > lists the possible types, and then foreign key the fruit_type column in > the fruits table to that to ensure nothing funky is entered. An enum > for type is another possibility. In the real world, this column actiually holds Operating Systems. I have 7 variants of Windows XP, even more of server, a dozen *nixes, etc, etc and it is fed from an external app. So I am looking for a magic query, or even a perl function to wrap up insde a procedure, whatever. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
