MYSQL query: count the dates

Question

In the table below

+-------+-----------------------+ 
| id    | timestamp             | 
+-------+-----------------------+ 
| 1     | 2010-06-10 14:35:30   | 
| 2     | 2010-06-10 15:27:35   | 
| 3     | 2010-06-10 16:39:36   | 
| 4     | 2010-06-11 14:55:30   | 
| 5     | 2010-06-11 18:45:31   | 
| 6     | 2010-06-12 20:25:31   | 
+-------+-----------------------+

I want to be able to count the dates (time is ignored). So the output should be like below:

+-------+-----------------------+ 
| id    | type         | count  |
+-------+-----------------------+ 
| 1     | 2010-06-10   | 3      |
| 2     | 2010-06-11   | 2      |
| 3     | 2010-06-12   | 1      |
+-------+-----------------------+

What would be the query for this?

Ignacio Vazquez-Abrams · Accepted Answer · 2010-06-30 13:52:39Z

5

This works if you can live without the id column in the result:

SELECT DATE(timestamp) AS type, COUNT(*) AS `count`
FROM sometable
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp)

answered Jun 30, 2010 at 13:52

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

VoodooChild Over a year ago

@MvanGeest, you don't need a distinct here because you are doing GROUP BY!

Ignacio Vazquez-Abrams Over a year ago

@VoodooChild: No, the reason you don't need DISTINCT here is because there are no joins.

VoodooChild Over a year ago

@Ignacio Vazquez-Abrams: Well he could have done [SELECT DISTINCT (DATE(timestamp)) from sometable] INSTEAD OF [SELECT (DATE(timestamp) as t from sometable group by t], which would produce the same result. So in this sense, he would not need distinct when using group by!

ceteras Over a year ago

This is correct. You don't need order by DATE(timestamp) because group by will automatically order it anyway (in mysql at least). Except if you want another order, such as order by 2 desc, to get the most frequent days first.

ceteras Over a year ago

@VoodooChild : I was not replying to your discussion, I was pointing to the fact that Ignacio's answer is correct and made a further suggestion.

Tom H · Accepted Answer · 2010-06-30 13:53:01Z

0

SELECT
    DATE(timestamp),
    COUNT(*)
FROM
    My_Table
GROUP BY
    DATE(timestampe)

This doesn't give you a row number for each row. I don't know if (why?) that's important.

answered Jun 30, 2010 at 13:53

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Comments

Ike Walker · Accepted Answer · 2010-06-30 13:53:14Z

0

select date(timestamp) as type, count(*)
from your_table
group by type;

answered Jun 30, 2010 at 13:53

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

2 Comments

Madhivanan Over a year ago

I doubt if alias name can be directly used in the GROUP BY Clause

Ike Walker Over a year ago

@Madhivanan: MySQL supports the use of aliases from the SELECT clause in GROUP BY, ORDER BY, and HAVING clauses. From the manual:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

VoodooChild · Accepted Answer · 2010-06-30 13:59:07Z

0

This might work...

select DATE(timestamp), count(timestamp)
    from _table
group by timestamp
order by count(timestamp) desc

answered Jun 30, 2010 at 13:59

VoodooChild

9,8049 gold badges70 silver badges102 bronze badges

Comments

Spellfire · Accepted Answer · 2010-06-30 14:00:46Z

0

SELECT count( DATE_FORMAT( timestamp, '%Y-%m-%d/' ) ) , DATE_FORMAT( timestamp, '%Y-%m-%d' ) FROM tablename group by DATE_FORMAT( timestamp, '%Y-%m-%d' );

You can not include the id in the select or the count will be off.

answered Jun 30, 2010 at 14:00

Spellfire

1

Collectives™ on Stack Overflow

MYSQL query: count the dates

5 Answers 5

5 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related