database - Mysql query to get count result and group by -


i've got 3 tables different records 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 tables want result set this,

 username |             realname       |     count evn-az-3ju           john                     2 zrobzh4um0           santa                    0 bsmiatwkhi           mahesh                   0

i want know roster jid username 'jwyvm_rdyt' has along realname , count of message if message contains roster jid in that. 'evn-az-3ju' in message 54 , 78, count 2 roster jid.

any appreciated.

select c.jid username,c.realname,count(nullif(trim(o.message), ''))  count  (select r.`jid`,u.realname roster r join user u on r.jid=u.username  r.username='jwyvm_rdyt' ) c  left  join offline o on o.message  concat('%', c.jid, '%') , o.username='jwyvm_rdyt' group jid

-

Comments

Post a Comment

Popular posts from this blog

angular - Is it possible to get native element for formControl? -

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

javascript - Why jQuery Select box change event is now working? -

i working on product filters user select select box according his/her preference not getting selected value user in jquery. html code:- <select name="product_filter" id="product_filter"> <option value="price_low_first">price : low high</option> <option value="price_high_first">price : high low</option> <option value="latest">latest</option> <option value="popular">most popular</option> </select> jquery code: $(function () { $("#product_filter").change(function () { alert("hi") var selectedtext = $(this).find("option:selected").text(); var selectedvalue = $(this).val(); alert("selected text: " + selectedtext + " value: " + selectedvalue); }); }); hi believe code perfect. once please check jquery.min.js
Read more