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

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]