Within the database I am working in, the column Notes is datatype varchar with a date value and a note placed right after (Example: 05/15/2024: This is the note.) There are two main comments I have extracted into another column using a Case When expression which is as follows:

Case When notes like "%Automatic%' then 'Automatic' ELSE 'Manual' End) as 'Status'

I am attempting to create a SUM CASE WHEN expression to give me the total number of Automatic rows and Manual rows but I am encountering a few error messages most commonly:

Operand data type varchar is invalid for sum operator.

which I suspect is because of the varchar datatype in notes section.

Is there a way to sum via the ‘Status’ Column that has been created through the case when expression?

Code as follows:

SELECT a.CustomerID
    ,SUM(CASE
        WHEN A.NOTES LIKE '%AUTOMATIC%' THEN 'AUTOMATIC' ELSE 'MANUAL' END) AS 'STATUS?'
FROM CARS.DBO.REVIEW AS A
GROUP BY A.CustomerID

Any insight would be appreciated