Re: how do i count() similar items

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,
thenstore the value, rather than the string, in the main table. 

Susan

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of jackassplus
Sent: Wednesday, September 08, 2010 12:22 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general