CODEWITHSUNDEEP
mysqlsqlgroup-bydistinct
Matthias
Matthias

Reputation: 13

MySQL - query - Mix between DISTINCT and GROUP BY

I have the following table:

SELECT * FROM tblTemp;
+-----+---------------------+
| id  | timestamp           |
+-----+---------------------+
|  84 | 2013-07-18 22:36:32 |
|  85 | 2013-07-18 22:36:33 |
|  88 | 2013-07-18 22:36:33 |
|  89 | 2013-07-18 22:36:34 |
| 263 | 2013-08-04 16:01:05 |
| 264 | 2013-08-04 16:01:11 |
| 272 | 2013-08-04 16:01:42 |
| 273 | 2013-08-04 16:01:42 |
| 274 | 2013-08-04 16:01:42 |
| 275 | 2013-08-04 16:21:25 |
| 276 | 2013-08-04 16:21:25 |
| 279 | 2013-08-04 16:21:26 |
| 280 | 2013-08-04 16:21:27 |
| 281 | 2013-08-04 16:21:27 |
+-----+---------------------+
14 rows in set (0.00 sec)

First i want to remove all double timestamps in the view like this

SELECT DISTINCT timestamp FROM tblTemp;
+---------------------+
| timestamp           |
+---------------------+
| 2013-07-18 22:36:32 |
| 2013-07-18 22:36:33 |
| 2013-07-18 22:36:34 |
| 2013-08-04 16:01:05 |
| 2013-08-04 16:01:11 |
| 2013-08-04 16:01:42 |
| 2013-08-04 16:21:25 |
| 2013-08-04 16:21:26 |
| 2013-08-04 16:21:27 |
+---------------------+
9 rows in set (0.00 sec)

But this is not enough. I need the count, how many entries are in the DB each hour (without doubles) - about this: 

+---------------------+-------+
| timestamp           | count |
+---------------------+-------+
| 2013-07-18 22:00:00 | 3     |
| 2013-08-04 16:00:00 | 6     |
+---------------------+-------|

First i thought i can use the following command:

SELECT timestamp, COUNT(id) FROM tblTemp GROUP BY YEAR(timestamp), MONTH(timestamp), DAY(timestamp), HOUR(timestamp)

But it doesn't work fine. The command counts all rows :/

+---------------------+-------+
| timestamp           | count |
+---------------------+-------+
| 2013-07-18 22:36:32 |     4 |
| 2013-08-04 16:01:05 |    10 |
+---------------------+-------+

It is not important, if the value in column timestamp is 2013-07-18 22:00:00 or 2013-07-18 22:36:32 or anything else as long as the time and date is identifiable.

Thanks :)

Upvotes: 0

Views: 293

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562310

SELECT DATE_FORMAT(timestamp,'%m/%d/%Y %H') AS date_hour, 
  COUNT(DISTINCT timestamp) 
FROM tblTemp 
GROUP BY date_hour

See http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_count-distinct

Upvotes: 3

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select DATE_FORMAT(`timestamp`, '%Y-%m-%d %H'), count(*)
 from (select distinct `timestamp` from Table1) abc
group by DATE_FORMAT(`timestamp`, '%Y-%m-%d %H');

fiddle

Upvotes: 0

Jafar Kofahi
Jafar Kofahi

Reputation: 763

maybe something like this (you might need to check the date format string)

SELECT DATE_FORMAT(timestamp,'%m/%d/%Y %H') timestamp, COUNT(id) 
FROM tblTemp 
GROUP BY DATE_FORMAT(timestamp,'%m/%d/%Y %H')

Upvotes: 0

Related Questions