Databases
StackOverflow2013
Postsdbo
POStrange behavior of mysql when group using date function
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStrange behavior of mysql when group using date function
    text
    copied!<p>Mysql seems to return wrong result when using COUNT(DISTINCT...) and GROUP BY DATE(...). I am grouping by "record_date" column and I expect to see distinct values in the result set, but this is not the case. Also I am not able to order the result for some reason.</p> <p>I do not know what is causing this behavior of MySQL.</p> <p><strong>My table:</strong></p> <pre><code>CREATE TABLE `test_table` ( `test_id` int(10) unsigned NOT NULL auto_increment, `rid` varchar(50) default NULL, `rid_test_id_ppl` int(10) unsigned NOT NULL, `record_time` datetime default NULL, PRIMARY KEY (`test_id`), UNIQUE KEY `uid` (`rid`), KEY `rid_test_id_ppl` (`rid_test_id_ppl`), KEY `record_time` (`record_time`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 </code></pre> <p><strong>My Query:</strong></p> <pre><code> SELECT DATE(`record_time`) AS record_date , COUNT( DISTINCT rid_test_id_ppl ) AS dubl FROM test_table GROUP BY record_date ORDER BY record_date ASC </code></pre> <p><strong>My Result:</strong></p> <pre><code>+------------+-------+ | record_date| dubl | +------------+-------+ | NULL | 58569 | | 2013-08-21 | 6052 | | 2013-08-22 | 19180 | | 2013-08-23 | 1 | | 2013-08-22 | 7632 | | 2013-08-23 | 1 | | 2013-08-22 | 1300 | | 2013-08-23 | 16458 | | NULL | 54996 | | 2013-08-22 | 1 | | NULL | 1750 | | 2013-08-21 | 2221 | +------------+-------+ 12 rows in set (0.36 sec) </code></pre> <p><strong>My Result, when I remove only the "DISTINCT" keyword:</strong></p> <pre><code>+------------+--------+ | record_date| dubl | +------------+--------+ | NULL | 121134 | | 2013-08-21 | 8275 | | 2013-08-22 | 28130 | | 2013-08-23 | 16694 | +------------+--------+ 4 rows in set (0.30 sec) </code></pre> <p>Edit: My MySQL version is 5.0.45. Edit: The result set in both cases is copy-pasted. One more strange thing is that I got no ordering of the results in the first query.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload