如何在SQL中的嵌套联接中使用GroupBy

下面的SQL语句返回用户名、IP地址、持续时间、通信量和上次访问。

            SELECT username, SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
                   SUM(acctsessiontime) as `duration`, count(username) as Count, 
                   (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes, 
                   MAX(acctstoptime) as `last visit`
            FROM radacct
            GROUP BY username

我想把这个加入国家表,并按国家分组,这样我就知道每个国家使用了多少流量,以及该国最后一次访问是什么时候。

    SELECT  c.country, 
            round(GBytes, 2),  
            Count, 
            duration as `Total Time Spent`,
            `last visit`
            FROM (
                SELECT username, 
                       SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,        
                       SUM(acctsessiontime) as `duration`, count(username) as Count,                  
                       (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
                       MAX(acctstoptime) as `last visit`
                FROM radacct
                GROUP BY username
            ) filtered_radacct
            JOIN u_cache_db.`global_ip` c ON c.ip = filtered_radacct.IP
            GROUP BY c.country
            order by GBytes DESC;