Mysql query to get count result and group by I've got three tables with different records for users,

User

username |        realname   |         date
evn-az-3ju           john      11/2012 03:09:40 p.m.
jwyvm_rdyt           steve      12/2012 03:09:40 p.m.
bsMIAtWkhi           mahesh      01/2013 03:09:40 p.m.
zrObzh4um0           santa      01/2013 03:09:40 p.m.
WyVm_rDYt           grolsch      11/2012 03:09:40 p.m.

offline

username |       messageID |    message
jwyvm_rdyt           54      &lt;message to="jwyvm_rdyt" id="t4Wa4-291" type="chat" from="evn-az-3ju"&gt;&lt;body&gt;test1&lt;/body&gt;&lt;thread&gt;1a327531-5a1c-4d6b-8b66-1209cdabb77d&lt;/thread&gt;&lt;/message&gt;
jwyvm_rdyt           78      &lt;message to="jwyvm_rdyt" id="t4Wa4-290" type="chat" from="evn-az-3ju"&gt;&lt;body&gt;Happy birthday&lt;/body&gt;&lt;thread&gt;1a327531-5a1c-4d6b-8b66-1209cdabb77d&lt;/thread&gt;&lt;/message&gt;
evn-az-3ju           89      &lt;message to="evn-az-3ju" id="t4Wa4-290" type="chat" from="evn-az-3ju"&gt;&lt;body&gt;Happy birthday&lt;/body&gt;&lt;thread&gt;1a327531-5a1c-4d6b-8b66-1209cdabb77d&lt;/thread&gt;&lt;/message&gt;
zrObzh4um0           98      &lt;message to="zrObzh4um0" id="t4Wa4-290" type="chat" from="evn-az-3ju"&gt;&lt;body&gt;Happy birthday&lt;/body&gt;&lt;thread&gt;1a327531-5a1c-4d6b-8b66-1209cdabb77d&lt;/thread&gt;&lt;/message&gt;
WyVm_rDYt           45      &lt;message to="WyVm_rDYt" id="t4Wa4-290" type="chat" from="evn-az-3ju"&gt;&lt;body&gt;Happy birthday&lt;/body&gt;&lt;thread&gt;1a327531-5a1c-4d6b-8b66-1209cdabb77d&lt;/thread&gt;&lt;/message&gt;

roster

username |             jid       |     date
jwyvm_rdyt           evn-az-3ju      11/2012 03:09:40 p.m.
jwyvm_rdyt           zrObzh4um0      12/2012 03:09:40 p.m.
jwyvm_rdyt           bsMIAtWkhi      01/2013 03:09:40 p.m.
zrObzh4um0           WyVm_rDYt      01/2013 03:09:40 p.m.
WyVm_rDYt           zrObzh4um0      11/2012 03:09:40 p.m.


These are the tables from which i want result set like this,

 username |             realname       |     count
evn-az-3ju           john                     2
zrObzh4um0           santa                    0
bsMIAtWkhi           mahesh                   0


I want to know all the roster jid which username 'jwyvm_rdyt' has along with their realname and count of message if that message contains any roster jid in that. Like 'evn-az-3ju' is in message 54 and 78, so count 2 for that roster jid.

Any help would be greatly appreciated.
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.