#1
  1. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13

    Group by daily and hourly


    How can I group by daily's hour? Below is my code written in MySQL to grab the lastest datetime from the table group by each day and hourly.

    PHP Code:
    /*--------------------------------*/
    /* MySQL */
    /*--------------------------------*/
    $sql_query "    SELECT MAX(graph_datetime) AS graph_datetime
                    FROM graph
                    WHERE match_id = 
    $match_id
                    GROUP BY match_id, DATE_FORMAT(graph_datetime,'%d/%m/%y %H')
                    ORDER BY match_id, graph_datetime"
    ;
    $match_result mysql_query($sql_query,$db); 
    However, when I do a direct conversion to postgreSQL, it doesn't seems to perform the way I expected.

    PHP Code:
    /*--------------------------------*/
    /* PostgreSQL */
    /*--------------------------------*/
        
    $sql_query "    SELECT MAX(graphdatetime) AS graph_datetime
                        FROM graph
                        WHERE matchindex = 
    $match_id
                        GROUP BY matchindex, TO_CHAR(graphdatetime,'%d/%m/%y %H')
                        ORDER BY matchindex, graphdatetime"
    ;
        
    $match_result pg_query($db,$sql_query); 
    This is the error message: Warning: pg_query() query failed: ERROR: Attribute graph.graphdatetime must be GROUPed or used in an aggregate function

    Please advise as I am new in postgreSQL. I am expecting the max(datetime) for each day and hourly.

    Many thanks in advanced.
    I have yet to find the right way to do it.

    www.genedavinci.com
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2002
    Posts
    9
    Rep Power
    0
    Remove "graphdatetime" field from ORDER BY clause.

    Or use the following:
    PHP Code:
        $sql_query "
    SELECT * FROM
      (
       SELECT matchindex, MAX(graphdatetime) AS graph_datetime
       FROM graph
       WHERE matchindex = 
    $match_id
       GROUP BY matchindex, TO_CHAR(graphdatetime,'%d/%m/%y %H')
      ) AS x_table
    ORDER BY matchindex, graph_datetime"


IMN logo majestic logo threadwatch logo seochat tools logo