按日期对一组数据进行分组

Question

The question is a little on the noobish end as I'm just starting to mess with PHP. However my project is to create a basic statistics page. So let me go ahead and explain exactly what I'm looking for (in it's most basic form).

Basically I have an table with the following structure. [ TransactionID, Date, Amount ]
What I want to do is group everything by a certain date and display all of the transactions that happened on that date, so I believe the query would look something like this.

Select * from table where date = 2014-05-24

Alright, that's all fine and dandy and I can easily get the sum like that, but How would I get the transactions for a certain month, or year even?

This is a personal project and won't be used at all on a professional scale, but it's something that I'm working on as a hobby.

Answer 1

If your Date column is of type DateTime, you may see date functions in Mysql.

To get all records for a specific date like 2014-05-24 :

Select * from table T where Date(T.date) = Date('2014-05-24')

To get all records for a specific year like 2014 :

Select * from table T where Year(T.date) = Year('2014-05-24')

For a specific month of year like 5th of 2014

Select * from table T where Year(T.date) = Year('2014-05-24') 
and 
Month(T.date) = Month('2014-05-24')