MySQL group by day with datetime mixing same dates from different months

Question

I'm trying to group posts from same day, the problem is that 2/20 gets grouped with 3/20 (20 = 20)

How can this be fixed?

This is my current code:

select day(Date), count(*) from Posts WHERE shopID != '' group by shopID, day(Date)

Interrobang · Accepted Answer · 2012-03-24 05:08:05Z

3

You need to group by every piece that might be different. So add MONTH(Date) and even YEAR(Date) depending on the scope of your query.

select DAY(Date), count(*) from Posts WHERE shopID != '' group by shopID, YEAR(Date), MONTH(Date), DAY(Date)

answered Mar 24, 2012 at 5:08

Interrobang

17.5k3 gold badges57 silver badges63 bronze badges

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

Comments

Mosty Mostacho · Accepted Answer · 2012-03-24 05:42:41Z

0

You could also group this way: UNIX_TIMESTAMP(date(date)), instead of grouping by year, month and day separately

select date(date), count(*) from Posts
WHERE shopID != ''
group by shopID, UNIX_TIMESTAMP(date(date))

Note you'll have to also take the other date data in the select statement to be able to recognize which month/year the day belongs to. If you don't you'll get a lot of day numbers and counts, but the day numbers will be repeated for each month/year.

That's why I used date(date), count(*).

edited Mar 24, 2012 at 5:42

answered Mar 24, 2012 at 5:32

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Collectives™ on Stack Overflow

MySQL group by day with datetime mixing same dates from different months

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related