mysql - Extract groups with multiple rows - 


create table `table`     (`source` varchar(20), `eventid` varchar(9), `system` varchar(10),`accountname` varchar(10))  ;  insert `table`     (`source`, `eventid`, `system` ,`accountname`) values     ('uatapp', '528', 'bxw','jbb'),     ('uatapp', '528', 'bxw','jbc'),     ('nxapp', '530', 'gg','ff'),     ('aaunswh20', '528', 'ccd','ff'),     ('172.1.12.3', '528','vv','ff'),            ('172.1.12.3', '528', 'vv', 'ff'),     ('172.1.12.3', '528','vv','ff3') ;

if use query

select   source,accountname,system, count(*)     `table`    eventid = '528' group source,accountname

i result 

source  accountname     system  count(*) 172.1.12.3  ff  vv  2 172.1.12.3  ff3     vv  1 aaunswh20   ff  ccd     1 uatapp  jbb     bxw     1 uatapp  jbc     bxw     1

i result show only

source  accountname     system  count(*) 172.1.12.3  ff  vv  2 172.1.12.3  ff3     vv  1 uatapp  jbb     bxw     1 uatapp  jbc     bxw     1

(ie) grouping of source,accountname multiple rows , not single row such aaunswh20.

can in formulating query?..thanks

the basic query rejecting 1 shows source , system, doesn't group accountname:

select source, system `table` eventid = '528' group source, system having count(distinct accountname) >= 2;

the problem is, can't see account names used. can accomplished putting query derived table, join original table, so:

select distinct    t.*    `table` t    inner join (       select source, system       `table`       eventid = '528'       group source, system       having count(distinct accountname) >= 2    ) d on t.source = d.source       , t.system = d.system    t.eventid = '528' ;

see working in sql fiddle.


Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more

C# WinForm - loading screen -

i ask how make loading screen (just picture or something) appears while program being loaded, , disappears when program has finished loading. in fancier versions, have seen process bar (%) displayed. how can have that, , how calculate % show on it? i know there form_load() event, not see form_loaded() event, or % property / attribute anywhere. all need create 1 form splash screen , show before main start showing landing page , close splash once landing page loaded. class splashform { //delegate cross thread call close private delegate void closedelegate(); //the type of form displayed splash screen. private static splashform splashform; static public void showsplashscreen() { // make sure launched once. if (splashform != null) return; thread thread = new thread(new threadstart(splashform.showform)); thread.isbackground = true; thread.setapartmentstate(apartmentstate.sta); thread....
Read more