Use CASE & COALESCE

Question

This syntax just gives me an unhelpful error of

Incorrect syntax near mentorname

What do I update to force this to show the results?

SELECT COALESCE(case when
            mentorname LIKE '%Med%' THEN 'MedTronics' end
            mentorname LIKE '%Zi%' THEN 'Zinamice' end
            , 'Total') As [Prov Source]

From database1

Answer 1

To keep this simple, you could accomplish the same thing with nested CASE statements and avoid COALESCE completely...

SELECT 
    [Prov Source] = 
        CASE WHEN mentorname LIKE '%Med%' THEN 
            'MedTronics' 
        ELSE
            CASE WHEN mentorname LIKE '%Zi%' THEN 
                'Zinamice' 
            ELSE
                'Total'
            END
        END

Answer 2

Try this:

SELECT COALESCE(CASE 
                    WHEN mentorname LIKE '%Med%' 
                        THEN 'MedTronics'
                    WHEN mentorname LIKE '%Zi%' 
                        THEN 'Zinamice' 
               END
            , 'Total') As [Prov Source]

Although you could simplify the query and get rid of the COALESCE altogether and leave just the CASE:

SELECT
    CASE 
        WHEN mentorname LIKE '%Med%'
            THEN 'MedTronics'
        WHEN mentorname LIKE '%Zi%'
            THEN 'Zinamice'
        ELSE 'Total'
    END AS [Prov Source]