Re: how do i count() similar items

On Wed, Sep 8, 2010 at 12:22 PM, jackassplus <jackassplus@gmail.com> wrote:
>
> <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.

If your column values can be grouped by regexp, you might be able to
get away with using a CASE statement.
eg.
select
sum(case when val ~* 'windows xp' then 1 else 0 end) as winxp,
sum(case when val ~* 'nix' then 1 else 0 end) as nix,
sum(case when val ~* 'redhat|rhel' then 1 else 0 end) as rh
....

(I don't have your original mail handy to reference the example values).