Counting total rows and grouping by a column in mysql -

-



so i've been looking @ 1 while , can't seem figure out.

i have mysql table following format , sample data:

id, customer, time, error code, duration  1,test1,00:12:00,400,120 2,test2,00:14:00,404,60 3,test1,00:15:00,404,120 4,test2,00:17:00,503,120 5,test1,00:19:00,400,60 6,test1,00:20:00,400,60 7,test2,00:21:00,503,60

i need results have customer name, total number of rows per customer name, error codes per customer, number of errors per customer , sum duration. above results want get:

test1, 4, 400, 3, 360 test1, 4, 404, 1, 360 test2, 2, 404, 1, 240 test2, 2, 503, 2, 240

the problem having when group error code, count number of rows , sum of duration grouped error code. need total count of rows , duration customer not total count of rows , duration customer per error code.

please let me know if need include else or if i'm super confusing am.

thank in advance help!

first need group calculated columns

 select customer, count(*) total_count, sum(duration) total_duration  yourtable  group  customer

and 

 select customer, error, count(*) error_count  yourtable  group  customer, error

then join table

select t1.customer,         t2.total_count,         t1.error,         t3.error_count,         t2.total_duration yourtable t1 join (      select customer, count(*) total_count, sum(duration) total_duration      yourtable      group  customer      ) t2   on t1.customer = t2.customer join (      select customer, error, count(*) error_count      yourtable      group  customer, error ) t3  on t1.customer = t3.customer , t1.error = t3.error group t1.customer        , t1.error




wiki

Comments

Post a Comment

Popular posts from this blog

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview....
Read more

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a...
Read more