In sql, group by using similar group_name

Create a separate table that explicitly maps each variation of group_name to its desired canonical new_group_name

populate this table with entries like:

JOIN your data table (tb1) with this mapping table on group_name = variation , and then GROUP BY the canonical_name from the mapping table.

SELECT
    gm.canonical_name AS new_group_name,
    SUM(t.val) AS total_val
FROM tb1 t
JOIN group_mapping gm ON t.group_name = gm.variation

GROUP BY gm.canonical_name
ORDER BY new_group_name;

If you:

• do know the canonical names
• do not know the mapping "variation: canonical" in advance
• have implemented the "is_similar()" function, that takes two strings and computes the similarity

You can compute a full join between your table of names and canonical names and use your "is_similar" function to compute the similarity between pairs. Then you replace the names from your first table with your remapped names and compute the aggregation.

Here's an example that gives you an idea of the involved SQL operations:

WITH remapping AS (
    SELECT all_names.string AS original_string,
           MAX(CASE WHEN IS_SIMILAR(all_names.string, canonical_names.string) > threshold
                    THEN canonical_names.string
                    ELSE all_names.string
               END) AS new_string
    FROM      (SELECT string FROM tb1) AS all_names
    FULL JOIN (SELECT string AS canonical_name 
               FROM tb1
               WHERE string IN <canonical_names_list>) canonical_names
    GROUP BY all_names.string
)
SELECT remapping.new_string,
       SUM(val) AS total
FROM      tb1
LEFT JOIN remapping
       ON tb1.string = remapping.original_string
GROUP BY remapping.new_string

There's a degree of complexity in actually defining the is_similar function, and given you would carry out this operations on all combinations of strings, it may easily become very slow with lots of data (and semantic similarity is next to impossible with SQL). You could attempt to reduce significantly the downtime and exploit ready-to-use tools in Python instead, including:

• the cdifflib library, faster implementation of the difflib library, it allows you to compute syntactic distance between words, it works through its main class SequenceMatcher, and comes with several helper functions. You can also play around with this using a bit of preprocessing (lowering characters, stemming/lemmatizing, changing the order of words - for multi-worded names, etc...)

from cdifflib import CSequenceMatcher

CSequenceMatcher(None, string1, string2).ratio()

• the rapidfuzz library, even faster than the cdifflib library, as it can parallelize the comparison between one string with an array (brings complexity from O(n)^2 to O(n) - you can actually go further to O(1) if you generate in advance all the combinations, at expense of physical memory). It gives similarity results that slightly differ with respect to the previous library (uses a different processor), I wouldn't say these are necessarily better in effectiveness.

from rapidfuzz import process, fuzz

process.extract(string1, strings2, scorer=fuzz.WRatio)

• the sentence_transformers library, you can select an LLM model among the available ones, it allows you to generate semantic embeddings given the words you have, and you can use the similarity function that comes with the library to compare the embeddings.

from sentence_transformers import SentenceTransformer, util

model = SentenceTransformer('multi-qa-mpnet-base-dot-v1')

embs1 = model.encode(strings1)
embs2 = model.encode(strings2)

float(util.cos_sim(embs1, embs2))

These are simple examples, but you should really go in depth with the docs and get creative with the parameters and functions.

In general I wouldn't throw in the semantic similarity for this use case, unless the names you have are well-formed ("AAPL" may not be semantically similar to "Apple", would rather have a higher syntactic similarity instead). If you really like to have both, you can use a weighted average of the two and fine-tune the weights according to the results you see.

Don't know your rdbms, but in Oracle there are SOUNDEX() function and UTL_MATCH Package functions EDIT_DISTANCE() and EDIT_DISTANCE_SIMILARITY() that could help.
Both could be done using python fuzzy.Soundex() and levenshteinDistance().
Below is one of the options how it could be solved with Oracle:

Create Table group_names (group_name VARCHAR2(32));

--      S a m p l e    D a t a :
Insert Into group_names
Select 'Apple Inc.' From Dual Union All 
Select 'AAPL' From Dual Union All 
Select 'Apple' From Dual Union All 
Select 'Microsoft' From Dual Union All 
Select 'MSFT' From Dual

--      S Q L : 
WITH
  grps AS
    ( Select     g0.group_name as G0_GRP_NAME, 
                 g1.group_name as G1_GRP_NAME,
                 SOUNDEX(g0.group_name) as G0_SOUNDEX,
                 SOUNDEX(g1.group_name) as G1_SOUNDEX,
                 UTL_MATCH.EDIT_DISTANCE(Upper(g0.group_name), Upper(g1.group_name)) as DISTANCE, 
                 UTL_MATCH.EDIT_DISTANCE_SIMILARITY(Upper(g0.group_name), Upper(g1.group_name)) as DISTANCE_PCT
      From       group_names g0
      Inner Join group_names g1 ON( Upper(g0.group_name) != Upper(g1.group_name) )
      Where      SubStr(SOUNDEX(g0.group_name), 1, 2) = SubStr(SOUNDEX(g1.group_name),1, 2)
      Order By   SOUNDEX(g0.group_name), UTL_MATCH.EDIT_DISTANCE(Upper(g0.group_name), Upper(g1.group_name))
   )
Select      Max(G0_GRP_NAME) as group_name
From        grps
Group By    SubStr(G0_SOUNDEX, 1, 2)

R e s u l t :

Above result is extracted from grps CTE which results as:

NOTE:
Not all columns are needed - I put them here as a sample ... You should find your own way to do the extraction that will fit your actual context.

fiddle