Weighing/Valuing shared strings using count on text array columns in Postgresql-CodePudding

I'm trying to find matching strings on a table of company data in a PostgreSQL database. I'm using the below query, which works fine for that purpose, but I want to weight some strings. Financial Services for example should be worth 3 "points" instead of a more generic thing like Mobile.

I was able to exclude some result by using the tag != 'Internet' where clause but I cant figure out a way to give extra weight for some strings.

SELECT
    count(*)
FROM (
    SELECT
        company_tags.tag
    FROM
        unnest(ARRAY ['Internet','Web Services & Apps','Technology','SAAS','Mobile', 'B2C', 'Financial Services']) AS company_tags (tag)
    WHERE
        tag != 'Internet'
        AND tag != 'Web Services & Apps'
        AND tag != 'Technology'
    INTERSECT
    SELECT
        company_pairing_option_tags.tag
    FROM
        unnest(ARRAY ['Internet','Technology','Web Services & Apps','Mobile','B2C', 'Financial Services']) AS company_pairing_option_tags (tag)
    WHERE
        tag != 'Internet'
        AND tag != 'Web Services & Apps'
        AND tag != 'Technology') AS count;

CodePudding user response：

you can replace count(*) by:

sum (
 case tag 
     when 'Financial Services' 
        then 3 
     when 'other 3 points tag' 
        then 3 
     else 1 
  end)