select count(answer1) as total, questionsResults.answer1
from questionsResults
WHERE correct=1 GROUP by answer1
My problem is that this groups lowercases and uppercases and even removes accents !
Bonjour pépé
bonjour pépé
Bonjour pepe
will all be grouped together : it will give me 1 row whereas I need 3 rows: I need distinguish lowercases, uppercases and even accents !
Any clue ?
From the manual
With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements.
This behaviour depends on the collocation in use.
The collocation you need is utf_bin.
You can directly specify it in the query using COLLATE
select count(answer1) as total, questionsResults.answer1
from questionsResults
WHERE correct=1 GROUP by answer1 COLLATE 'utf8_bin'
The documentation can be found here:
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
of course you can also modify the collocation in your database.
The other way round, grouping by it can be enforced as follows (this was my original answer, i mis understood the question. But for completeness i will leave it)
You can use string functions in your query.
Something like:
GROUP by replace(lower(answer1),'é',e)
If this is french, than not so much accents are possible so this is a solution.
You can also import functions into mysql, for example this one: https://github.com/falcacibar/mysql-routines-collection/blob/master/tr.func.sql (MySQL UDF implementation of strtr C function)
Or you could normalize the data before inserting it.
