Lee Lee - 7 months ago 16
SQL Question

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_UPLOAD 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

Answer

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