sorting - MySQL DATETIME sort with virtual datetable -

i have table events in conjunction timestamp datetime. want have statistic data, e.g. how events per day... in cases don´t have events every day , of course don´t in statistic days no existens entries.

| id | date           | count      | | 1  | 2014-09-06     | 1          | | 2  | 2014-09-07     | 8          | | 3  | 2014-09-10     | 2          | | 4  | 2014-09-14     | 78         | 

so wrote little script generates me query solv problem. generates virtual table days want know , left outer join event table. got dates without gaps! query looks e.g.:

select date_format(d.date, '%d.%m.%y') datum, count(l.id) anzahl  (  select str_to_date('25.11.2014', '%d.%m.%y') date union select str_to_date('26.11.2014', '%d.%m.%y') date union select str_to_date('27.11.2014', '%d.%m.%y') date union  select str_to_date('28.11.2014', '%d.%m.%y') date union  select str_to_date('29.11.2014', '%d.%m.%y') date union  select str_to_date('30.11.2014', '%d.%m.%y') date union  select str_to_date('01.12.2014', '%d.%m.%y') date ) d  left outer join events l on d.date = date(l.date)  group datum  order datum desc 

this query works absolute , have dates no data in statistic.

but comes real problem have: sorting doesn´t work! got weird output. have no idea problem is. output looks this:

| date          | count | | 31.10.2014    | 0     | | 30.11.2014    | 5     | | 30.10.2014    | 0     | | 29.11.2014    | 0     | | 29.10.2014    | 0     | | 28.11.2014    | 0     | | 28.10.2014    | 0     | | 27.11.2014    | 0     | | 27.10.2014    | 0     | | 26.11.2014    | 0     | | 26.10.2014    | 0     | | 25.11.2014    | 1     | | 25.10.2014    | 0     | | 24.11.2014    | 1     | | 24.10.2014    | 0     | | 23.11.2014    | 0     | | 23.10.2014    | 0     | | 22.11.2014    | 0     | | 22.10.2014    | 0     | | 21.11.2014    | 1     | | 21.10.2014    | 0     | | 20.11.2014    | 0     | | 20.10.2014    | 0     | | 19.11.2014    | 2     | | 19.10.2014    | 0     | | 18.11.2014    | 0     | | 18.10.2014    | 0     | | 17.11.2014    | 0     | | 17.10.2014    | 0     | | 16.11.2014    | 0     | 

so what´s wrong query? have conscious use function str_to_date got "real" date format. normaly sorting should work it, isn´t it?

problem eyes see them dates, in quesry they're strings correctly sorted string day , month first. try having anotehr field formated sorting , use sort.

select date_format(d.date, '%d.%m.%y') datum, date_format(d.date, '%y.%m.%d') sortdatum, count(l.id) anzahl  (  select str_to_date('25.11.2014', '%d.%m.%y') date union select str_to_date('26.11.2014', '%d.%m.%y') date union select str_to_date('27.11.2014', '%d.%m.%y') date union  select str_to_date('28.11.2014', '%d.%m.%y') date union  select str_to_date('29.11.2014', '%d.%m.%y') date union  select str_to_date('30.11.2014', '%d.%m.%y') date union  select str_to_date('01.12.2014', '%d.%m.%y') date ) d  left outer join events l on d.date = date(l.date)  group datum  order sortdatum desc