how to select to count with mysql

Question

i want to count with using select, count, and group by. it work with MariaDB but not working wit MySQL.

i dont know if that version of mariadb or mysql but this code working in localhost but not working in my vps.

here my code

SELECT date, COUNT(id_master_post) FROM master_post GROUP BY DAY(date) ASC;

error i get.

SELECT date, COUNT(id_master_post) FROM master_post GROUP BY DAY(date) ASC LIMIT 0, 25 MySQL states: Documentation

.#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'piratefiles.master_post.date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Tim Biegeleisen · Accepted Answer · 2018-01-18 11:17:30Z

2

This is a common error in MySQL, which has different modes and hence different rules which may or may not be enforced. In your case, you are selecting a column which does not appear in the GROUP BY clause (and also is not inside an aggregate function). To fix this, make the column being selected and aggregated the same, i.e. use this:

SELECT DAY(date), COUNT(id_master_post)
FROM master_post
GROUP BY DAY(date);

But this would group all dates together based on the numeric day. This may not be what you want, and in general I would recommend grouping on the date itself:

SELECT date, COUNT(id_master_post)
FROM master_post
GROUP BY date;

answered Jan 18, 2018 at 11:17

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

5 Comments

AniSong Lyrics Over a year ago

is that posible to showing full date not just day of that date? im using datetime. if im only using date it will count all of it, for ex if im posting 20 post taht day, it will record 20 data, but if using day(date) it will record just 1 data

Tim Biegeleisen Over a year ago

I don't understand your question.

Tim Biegeleisen Over a year ago

OK...so what about that result set do you not like/want to change?

AniSong Lyrics Over a year ago

i want the result like my screenshot, but the code not working in my vps. if im using u code it will only showing day of that date.

Tim Biegeleisen Over a year ago

My second query should generate that output. Try it.

Collectives™ on Stack Overflow

how to select to count with mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related