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.