grouping similar names together in Mysql as unique record

Question

Suppose I have a table with the following rows

id     Name                          Price      Supplier
1      Hello Apartment                $12       A
2      Hello Apartments               $8        B    
3      Itercontinental Hotel          $9        A
4      Hilton                         $18       C
5      Hello Apts                     $9        C
6      Apratrmentos Hello             $12       D
7      Ramada Hotel                   $8        A

When I call my results, I wish id 1,2,5 and 6 to be classed as 1 unique item, so the result set should look like:

Hello Apts                $8    (4 offers in total)
Itercontinental Hotel     $9
Hilton                    $18
Ramada Hotel              $8

In essence, I want to group similar names. Is this possible in MySQL, considering that there will be hundreds of other names that need to be grouped as well?

Any advice is much appreciated.

read this threads: http://stackoverflow.com/questions/634995/implementation-of-levenshtein-distance-for-mysql-fuzzy-search and http://stackoverflow.com/questions/369755/how-do-i-do-a-fuzzy-match-of-company-names-in-mysql-with-php-for-auto-complete — golimar, May 03 '12 at 11:06

score 0 · Accepted Answer · answered May 03 '12 at 11:02

Maybe something like this:

SELECT
    SUM(T.Price) AS price,
    T.Name
FROM
(
    SELECT
        Price,
        (
            CASE WHEN Name LIKE 'Hello Ap%'
            THEN 'Hello Apts'
            ELSE Name
        ) AS Name
    FROM
        table1
) AS T
GROUP BY
    T.Name

grouping similar names together in Mysql as unique record

1 Answers1