CODEWITHSUNDEEP
phpmysqlsql
Christian Tucker
Christian Tucker

Reputation: 627

Grouping a set of data by date

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.

Upvotes: 0

Views: 68

Answers (3)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

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')

Upvotes: 1

Kzoty
Kzoty

Reputation: 55

There are better ways than my example bellow, but a basic way could be :

SELECT * FROM tablename WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59

Upvotes: 0

Victory
Victory

Reputation: 5890

Try using the EXTRACT() FUNCTION, e.g.

     select * from table where EXTRACT(YEAR FROM `date`) = '2014'

     select * from table where EXTRACT(YEAR_MONTH FROM `date`) = '201405'

see date and time functions

Upvotes: 1

Related Questions