Mysql query to get count result and group by

countgroup byjoin;MySQLsubquery

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      <message to="jwyvm_rdyt" id="t4Wa4-291" type="chat" from="evn-az-3ju"><body>test1</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
jwyvm_rdyt           78      <message to="jwyvm_rdyt" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
evn-az-3ju           89      <message to="evn-az-3ju" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
zrObzh4um0           98      <message to="zrObzh4um0" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
WyVm_rDYt           45      <message to="WyVm_rDYt" id="t4Wa4-290" type="chat" from="evn-az-3ju"><body>Happy birthday</body><thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>

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.

Best Answer

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.

Related Question