CODEWITHSUNDEEP
mysqlcount
Kale
Kale

Reputation: 599

Grouping rows from a single day into a count - MYSQL

I've been trying to work through this problem for a bit, and clearly I'm missing something (probably something obvious).

I'm trying to group rows from a single day into a count. So the output should look like this:

Date         Count
2009-09-12   2
2009-09-13   5
2010-01-09   4

...and so on.

My current SQL looks like this:

SELECT `date`, COUNT(*) FROM `sales_flat_table` GROUP BY `date`;

And outputs data that looks like this: 

Date        Count
2009-09-12  1
2009-09-12  1
2009-09-13  1
2009-09-13  1
2009-09-13  1
2009-09-13  1
2009-09-13  1

...and so on.

What am I missing? Thanks!

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

My best guess is that date is really a datetime and it has a time component. To get just the date, use the date() function:

SELECT date(`date`) as `date`, COUNT(*)
FROM `sales_flat_table`
GROUP BY date(`date`);

Upvotes: 1

Related Questions