How to group by with similar group_name in sql

Question

How can I perform a

 GROUP BY

in SQL when the group_name values are similar but not exactly the same?

In my dataset, the group_name values may differ slightly (e.g., "Apple Inc.", "AAPL", "Apple"), but conceptually they refer to the same entity. The similarity might not be obvious or consistent, so I might need to define a custom rule or function like is_similar() to cluster them.

For simple cases, I can extract a common pattern using regex or string functions (e.g., strip suffixes, lowercase, take prefixes). But how should I handle more complex scenarios, like fuzzy or semantic similarity?

Case:

group_name	val
'Apple Inc.'	100
'AAPL'	50
'Apple'	30
'Microsoft'	80
'MSFT'	70

What I want to achieve:

new_group_name	total_val
'Apple'	180
'Microsoft'	150

What are the best approaches to achieve this in SQL? And how would I write a query like this:

SELECT some_characteristic(group_name) AS new_group_name,
       SUM(val)
FROM tb1
GROUP BY new_group_name;

score 6 · Answer 1 · answered May 15 '25 at 05:31

To group similar group_name values in SQL when they differ slightly (e.g., "Apple Inc.", "AAPL", "Apple"), you need to standardize or map them to a common new_group_name before applying GROUP BY. Since similarity isn't always consistent, simple string functions may not suffice for complex cases like fuzzy or semantic similarity. For your example, you can use a CASE statement or a mapping table to define rules for clustering, then group and sum.

For the given dataset, a CASE statement can map similar names to a canonical name:

SELECT 
    CASE 
        WHEN group_name LIKE '%Apple%' OR group_name = 'AAPL' THEN 'Apple'
        WHEN group_name LIKE '%Microsoft%' OR group_name = 'MSFT' THEN 'Microsoft'
        ELSE group_name 
    END AS new_group_name,
    SUM(val) AS total_val
FROM tb1
GROUP BY new_group_name;

This outputs Apple: 180, Microsoft: 150 by mapping "Apple Inc.", "AAPL", and "Apple" to "Apple", and "Microsoft" and "MSFT" to "Microsoft".

For more complex scenarios, create a mapping table to define relationships:

CREATE TABLE name_mappings (
    group_name VARCHAR(255),
    new_group_name VARCHAR(255)
);
INSERT INTO name_mappings VALUES
    ('Apple Inc.', 'Apple'),
    ('AAPL', 'Apple'),
    ('Apple', 'Apple'),
    ('Microsoft', 'Microsoft'),
    ('MSFT', 'Microsoft');

Then join and group:

SELECT m.new_group_name, SUM(t.val) AS total_val
FROM tb1 t
LEFT JOIN name_mappings m ON t.group_name = m.group_name
GROUP BY m.new_group_name;

For fuzzy matching, some SQL databases offer extensions (e.g., PostgreSQL's fuzzystrmatch with levenshtein), but MySQL/MariaDB lacks native fuzzy support. You can approximate with SOUNDEX or LIKE, though they’re less precise. For semantic similarity, preprocess data outside SQL (e.g., Python with NLP libraries) to generate mappings, then load them into a mapping table.

The CASE approach is simplest for your case, while a mapping table scales better for larger datasets. If fuzzy matching is needed, clarify your SQL engine for specific functions, or share more details about similarity rules.

How to group by with similar group_name in sql

1 Answers1