How do we refer to certain values in a column by an umbrella term in an SQL query?-CodePudding

I have a list of performers and their respective instruments in a table called 'playson'.

Among these instruments are a variety of guitars such as lead guitar, rhythm guitar and acoustic guitar.

I want to count the number of distinct instruments. For example, the list looks like this:

create or replace view instrument_count
as
select distinct instrument from playson

music=# select * from instrument_count;
   instrument    
-----------------
 violin
 guitars
 lead guitar
 synthesizer
 percussion
 mandolin
 flute
 rythm guitar
 bass
 keyboard
 drums
 banjo
 tambourine
 saxophone
 acoustic guitar
(15 rows)

HOWEVER, the issue is that I want to count acoustic, lead and rhythm guitars as simply 'guitar' (i.e. as one unique instrument).

So the count should be 12 distinct instruments, as 'guitars' accounts for acoustic, lead and rhythm.

Can I categorise those guitars under an umbrella term during a query? I would normally just exclude acoustic, rhythm and lead from the count but I run into the issue where a performer plays only 'lead guitar' but since my unique instrument list includes 'guitars' and excludes 'lead guitar', they are not recognised as playing any instrument at all.

It would be useful if I could create a view that counts 'guitars', 'lead guitar', 'rhythm guitar' and 'acoustic guitar' as simply 'guitar' in all instances across all tables.

I cannot alter the original columns. I can only use views

UPDATE: I have tried this:

create or replace view instrument_list
as
select performer, replace(replace(replace(instrument, 'lead guitar', 'guitars'),'rythm guitar' ,'guitars'),'acoustic guitar' ,'guitars' ) as instrument
from playson
;

-- Compute total instruments
create or replace view instrument_count
as
select distinct instrument from instrument_list

This seems to do the trick but it's awfully messy. Any more eloquent pathways? Thanks.

CodePudding user response：

Would something like:

Select( distinct case when instrument like '%guitar%' then 'guitar' else instrument end)

be what you're looking for. Here you get all distinct instruments and when one contains the string guitar it is simplified to guitar.