Avatar of creativepragma
creativepragma
 asked on

There is a command in MySQL that can show montlhy entries on a database ?

This is just a simple question because I don't know so much about MySQL. There is any command that can show the total of entries in a month?. Something like this:

2010-08-10 Total entries: 8
2010-10-11 Total entries: 9

I just need to know which command in MySQL can show me the total entry in one month.
MySQL Server

Avatar of undefined
Last Comment
johanntagle

8/22/2022 - Mon
theGhost_k8

you can use group by month(DATECOLUMN);

select month(DATECOLUMN) as Month,concat('total entries',count(*)) as totalEntries from tablename group by month(DATECOLUMN);
johanntagle

Monthly or daily?  Your example seems to mean daily.

Anyway for monthly:

select month(datetime_column), year(datetime_column), count(whatever_preferably_indexed_column) from table_name group by month(datetime_column), year(datetime_column);

If daily

select date(datetime column), count(whatever_preferably_indexed_column) from table_name
group by daate(datetime_column);
johanntagle

Note that I included year(datetime_column) because your table might span multiple years.  Without it, you will count entries for january of 2009 and 2010 together, for example.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
creativepragma

ASKER
Here is an example what I want, and it is a monthly entry not daily. Just supose that in january only eleven persons access to a webpage , So I should see something  like this on mysql:

+----------+----------------+
|Month     | Total Entries |
+----------+----------------+
| 01          |            3        |
+----------+----------------+
johanntagle

Have you even tested the sample code already given to you?   I guess the only thing missing there is to alias the table columns.  I would rather point you to the right page where you can learn it:

http://dev.mysql.com/doc/refman/5.1/en/select.html

then search the page for "alias"
creativepragma

ASKER
I have run all the MySQL commands but how I can make that this one just show me one month and not all the months ?. As I said before, I just need that the SQL command shows me one month not all of them

select month(06) as Month,concat('total entries',count(*)) as totalEntries from sqlguestbook group by month(06);

I just tried to used but I get the following screen:


+----------+----------------+
|Month     | Total Entries |
+----------+----------------+
| NULL     |            3        |
+----------+----------------+

It never show me the month just the word NULL and not the month (which is june as you see on the command).

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
johanntagle

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
creativepragma

ASKER
When I write this command on MySQL:

Select '06' as Month count(*) as TotalEntries from totalEntries Where month(datetime_column)=6 and year(datetime_column)=2011

I get this error message:

ERROR 1064 (42000): 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 'count(*) as TotalEntries from totalEntries Where month (entry_date)=6 and year(e' at line 1
johanntagle

Typo. Add a comma before count.