Use a CASE statement?
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.
From: email@example.com On Behalf Of jackassplus
Sent: Wednesday, September 08, 2010 12:22 PM
Subject: Re: [GENERAL] how do i count() similar items
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.