MySQL Query - Group By issues

The following MySQL query produces me a list of session_ids and associated usage. What i would like to do is group each session into one row with the greatest upload and download displaying. There can be multiple repeats of a user name, it has to be grouped on the session.

When I try and use group by, the greatest is not always selected.

  SELECT USERNAME, ACCTSESSIONID,
         IFNULL(ACCTINPUTGW ,0) * POW(2,32) + IFNULL(ACCTINPUTOCT , 0) as TOTAL_UPLOAD,
         IFNULL(ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(ACCTOUTPUTOCT, 0) as TOTAL_DOWNLOAD
    FROM ACCOUNTING
   WHERE DATE_FORMAT(FROM_UNIXTIME(TIME_STAMP), '%Y-%m-%d') = '2011-07-05'
ORDER BY USERNAME ASC, ACCTSESSIONID 

-

USERNAME    ACCTSESSIONID        TOTAL_UPLOA开发者_StackOverflow中文版D   TOTAL_DOWNLOAD
kor1        SESSION232442        341594114     5671726599
kor1        SESSION232442        331306202     5571382940
kor1        SESSION232444        338083784     5609510490
kor1        SESSION454355        323367019     5451121083
kor2        SESSION943209        323132957     5450522047  
ran32       SESSION934082        323132957     5450522047
ran62       SESSIONA34324        9532356       5450523537

You should use MIN()/MAX() aggregate functions for this:

SELECT USERNAME, ACCTSESSIONID,
     MAX(IFNULL(ACCTINPUTGW ,0) * POW(2,32) + IFNULL(ACCTINPUTOCT , 0)) as TOTAL_UPLOAD,
     MAX(IFNULL(ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(ACCTOUTPUTOCT, 0)) as TOTAL_DOWNLOAD
FROM ACCOUNTING
WHERE
    DATE_FORMAT(FROM_UNIXTIME(TIME_STAMP), '%Y-%m-%d') = '2011-07-05'
GROUP BY
    USERNAME, ACCTSESSIONID
ORDER BY
    USERNAME ASC, ACCTSESSIONID 

More about aggregate function in MySQL: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

  SELECT USERNAME, ACCTSESSIONID,
         MAX(IFNULL(ACCTINPUTGW ,0) * POW(2,32) + IFNULL(ACCTINPUTOCT , 0))
           AS TOTAL_UPLOAD,
         MAX(IFNULL(ACCTOUTPUTGW,0) * POW(2,32) + IFNULL(ACCTOUTPUTOCT, 0))
           AS TOTAL_DOWNLOAD
    FROM ACCOUNTING
   WHERE DATE_FORMAT(FROM_UNIXTIME(TIME_STAMP), '%Y-%m-%d') = '2011-07-05'
GROUP BY USERNAME ASC, ACCTSESSIONID 
ORDER BY USERNAME ASC, ACCTSESSIONID 

Note 1: Instead of IFNULL(), you can also use COALESCE(). It may be preferable as it can have more than 2 arguments and it's also used in many other RDBMSs.

Note 2: Instead of:

DATE_FORMAT(FROM_UNIXTIME(TIME_STAMP), '%Y-%m-%d') = '2011-07-05'

you can use:

TIME_STAMP >= '2011-07-05'  AND  TIME_STAMP < '2011-07-06'

No need to call 2 functions for every row in the table.