Mysql query to get count result and group by
SELECT r.username,
       u.realname,
       ( SELECT  COUNT(*)
           FROM offline
           WHERE username = r.username
             AND message LIKE CONCAT('%', r.jid, '%')
       ) as "count"
    FROM roster AS r
    JOIN User AS u  ON r.username = u.username
    WHERE r.username = 'jwyvm_rdyt'


Sometimes it is best to do a COUNT in a subquery in the SELECT clause - to avoid over-counting when doing a JOIN.  And, I think, GROUP BY is avoided.