how can we only have data for the rows that have a column only like 'ALLtrack', column not having 'GOLFalltrack' and including the count for examples above where we have both in as a same column?
It would be better that you don't have this attribute-data as a comma delimited list concatenated to make the value for a single column. If it was me I would have a separate table with a Key/Value column pair - the KEY would be the ID of the parent record, and the VALUE would be the attribute ALLTRACK or GOLFALLTRACK.
If you wanted to you could enforce validation on the VALUE - i.e. only Values that exist in some other table of ValidValues. At the very least you could report on the frequency of the actual values used, and do something about the ones that appear very infrequently (which are likely to be wrong-spelling or synonyms)
In the mean time you could use a SPLITER function that would separate the values in your column so that you could treat them as a pseudo-child table, and then make your test (based on EQUALS rather than LIKE).
If your search for LIKE '%alltrack%' is ONLY intended to match the whole-word ALLTRACK and provided that all your data is carefully comma delimited - with no spaces or other separating characters - then you could do
where ',' + column + ',' like '%,alltrack,%'
and ',' + column + ',' not like '%,golfalltrack,%'
but performance will be dreadful as SQL will not use any suitable indexes. If you only have a few thousand rows you won't notice, but it will scale very badly.
I think you'd need to ensure there was a prefix on the Column (and perhaps a suffix too), e.g.
where ',' + column like '%[^a-z]alltrack%'
or
where ',' + column + ',' like '%[^a-z]alltrack[^a-z]%'
that would also work for any other, bogus, separating symbols, or spaces, which might exist in the data (assuming that pure comma-separated is not guaranteed)
