[SOLVED] Group by various numbers together

[asmith]

Hi,

 

I have this table that hold records like posts. Each post (row) have a time that it has been posted in. This time was  php time() when recording.

 

I wanna get some stats about posts, Like how many were posted in each day. So I'm thinking about a simple group by and counting the rows. Is it possible to group data that does not have same value but are between value a and b?

 

How would you write a query for that?

Thanks for your time

[kickstart]

Hi

 

You probably could do a group by a range if you played around with a subselect and a case statement.

 

However probably easiest to format the date (ie, maybe to year and week if you wanted the number of posts per week) and then group by that.

 

Note one minor issue that might cause you a problem if you want to draw a graph from this data. Any time period with no posts will not return 0 but will just have no row, which might confuse any graph class you use. You can code round it in php or play around doing an outer join against another select to return all the date / time ranges you are interested in.

 

All the best

 

Keith

[asmith]

I got it working.

 

Thanks for the info mate. I wrote the graph myself

I also found some useful functions in mysql.

 

Thanks again!