#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0

    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
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,581
    Rep Power
    1906
    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):
    Code:
    SELECT  MONTH(DateColumn) as Month
    	  , rating
    	  , COUNT(Rating) as NumberOfRating
    FROM Table
    GROUP BY MONTH(DateColumn), rating
    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:

    Code:
    Month	Rating	NumberOfRating
    08	3	72
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0
    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.
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,581
    Rep Power
    1906
    Originally Posted by MackenzieMcDani
    That said, it may be easier to use this select statement and manipulate the data using PHP.
    You should indeed only use SQL is to get the minimum required data and then work and format the data within PHP.

IMN logo majestic logo threadwatch logo seochat tools logo