Mysql group by included the whitespace as same value

I have this table

CREATE TABLE table1 (
  `ID` VARCHAR(100),
  `Val` VARCHAR(100),
  `Val2` VARCHAR(100)
);

and this value

INSERT INTO table1
  (`ID`, `Val`, `Val2`)
VALUES
  ('1','1234 ','now'),  // 1 whitespace
  ('2','1234  ','now1'), // 2 whitespace
  ('5','1234  ','now190'), // 2 whitespace
  ('3','1234   ','now2'), // 3 whitespace
  ('4','3123123','now3')

I need to group by the data and count how many data that have same value, so i used group by and count

select Val,count(*) from `table1` group by Val

the result not what i expect because the data for ID 1,2,5, and 3 is counted as same value like below result

Val count(*)
1234    4
3123123 1

how could i make the result like expected result below so the value didn’t count as same value

Val count(*)
1234    1  // 1 whitespace
1234    2  // 2 whitespace
1234    1  // 3 whitespace
3123123 1

see this fiddle for demo

Answer

This is just how MySQL does it by default, unless you use some specific collation. A typical workaround is to use binary:

select binary val, count(*) cnt from table1 group by binary val

Or, if you do want a regular string in the resultset rather than a binary string:

select max(val) as newval, count(*) cnt from table1 group by binary val