Link to home
Create AccountLog in
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.
Avatar of theGhost_k8
theGhost_k8
Flag of India image

you can use group by month(DATECOLUMN);

select month(DATECOLUMN) as Month,concat('total entries',count(*)) as totalEntries from tablename group by month(DATECOLUMN);
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);
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.
Avatar of creativepragma
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        |
+----------+----------------+
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"
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).

ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Typo. Add a comma before count.