display count of numeric and non numeric column value in a single row in mysql

Question

I have a table test with a column names which have both integer as well as non integer values

+------------+
| names      |
+------------+
| 123        |
| 123abc     |
| 89         |
| dkkjdk     |
| dkdn       |
+------------+

I would like to display the count of integer and non integer values in a single row like this

integer_count non_integer_count
2               3

I tried to print integer values using this query

select cast(names as signed) as int_val from test;

But I got this result

+---------+
| int_val |
+---------+
|     123 |
|     123 |
|      89 |
|       0 |
|       0 |
+---------+

The name field is a varchar(20) field.

Answer 1

Here is the solution

SELECT COUNT(CASE WHEN not names REGEXP '^[0-9]+$' THEN 1 END) AS ALPHA, COUNT(CASE WHEN names REGEXP '^[0-9]+$' THEN 1 END) AS DIGIT FROM test;

Output

+-------+-------+
| ALPHA | DIGIT |
+-------+-------+
|     3 |     2 |
+-------+-------+