Help with Aggregated SQL Statement (similar to a crosstab?)

Hi. I've been banging my head against this for a while so I thought I may ask for help.

In its simplest form, a table has 2 columns. One is date / time, the other is a rating of 0-10.

What I am trying to accomplish is aggregating the data for use in a graph. I want to re-render the data to be:

Columns:

• Month
• 0
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

In the month column, I believe I should use MONTH(DateColumn) and some variation of an If or CASE statement (would like to store as "August", September", etc...). In each new ranking column, I want a simple tally. Thus, If a response a "3" is provided 72 times when the MONTH(DateColumn) = 8 (aka August), in the row where August is listed as the Month, I would like 72 listed in column '3'.

Does this make sense?

I'm studying up on if/then's and case statements but this level of conditional select logic is quite new to me.

Thank you for any help.

-MM

Not exactly sure if this is what you want, would be nice if you had include description of the the table.

Maybe something like this will be what you need (not tested):


but it does not have number for ratings not given.
For example, if only "3" have been given as rating in August it will return:



For changing 08 to the name of the month, take a look at this place:
http://mysql-tips.blogspot.dk/2005/04/mysql-select-case-example.html

Thanks, Fujin. That does summarize the data to the data points that I'm looking for. I guess I was looking to summarize them into a specific format as well.

And your interpretation of the structure of the table / columns was on target. Its truly a simple table (date field, rating field).

On the backend, I'm using the Google Charts API for some graphing functions; thus the format / layout of the data does play a role. That said, it may be easier to use this select statement and manipulate the data using PHP.

My latest rendition of the SQL statement that delivers the format I want is a massive if/then UNION statement (and 5x as long to execute). Likely more server intensive than it needs to be.

I appreciate the help.

You should indeed only use SQL is to get the minimum required data and then work and format the data within PHP.