there a lot wrong with your query :
1) Do you think COUNT(COLUMN2||COLUMN3||COLUMN4) will return something else than COUNT(*) ? you probably should have kept the DISTINCT from your first post.
2) HAVING-clause should only be used when evaluating aggregate functions result (like count() or sum() ). Normal column predicates should be in the where clause. To put it simply : Where clause is evaluated during data-reading, Having-clause is evaluated afterwards on the result set.
Now for an answer to your question. There are a few ways to solve your problem, none very performant.
Combining my previous post on how to find non-numeric data and your query, with a CASE-clause you could solve it this way :
,SUM(case when translate(COLUMN5,'X ',' 0123456789') <> ' ' then 0
else INTEGER(COLUMN5) end)
WHERE COLUMN1 <> 'VALUE1'
GROUP BY COLUMN1
I can explain it to you, but i can not understand it for you.