sql group_by count

i have a database like this:

I want to count id by month like this result :

id creat_time

6 2018-8


I tried this query:

"SELECT EXTRACT(YEAR_MONTH from creat_time),COUNT(id) FROM b_project WHERE creat_time GROUP_BY EXTRACT(YEAR_MONTH from creat_time)"


But I got the following error:

1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'GROUP_BY EXTRACT(YEAR_MONTH from creat_time)' at line 1

How to fix it?

WHERE

WHERE

WHERE creat_time

2 Answers
2

This is your existing query:

SELECT EXTRACT(YEAR_MONTH from creat_time),COUNT(id)
FROM b_project
WHERE creat_time
GROUP_BY EXTRACT(YEAR_MONTH from creat_time)


The where clause is incomplete, that will cause an error. You need to do something to compare the column to something: e.g.

and, according to an image in your question the column name is create_time, so that needs correction as well, e.g.

SELECT EXTRACT(YEAR_MONTH from create_time),COUNT(id)
FROM b_project
WHERE create_time > '2018-09-01'
GROUP_BY EXTRACT(YEAR_MONTH from create_time)


MySQL is trying to point us to the error. But it can only tell us what line# has the problem. Since the SELECT is all on one line, we don't get much information. Please reformat the Select as follows, putting each item on a separate line.

Also, GROUP_BY might probably be two separate words without any hyphen, so try that.

SELECT
Extract(YEAR_MONTH FROM create_time),
Count(id)

FROM b_project

GROUP BY
Extract(YEAR_MONTH FROM create_time)


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.