MySQL: Union with many Count and Group By -



MySQL: Union with many Count and Group By -

select t.tipificacao1, t.tipificacao2, count(c.id) total0, 0 total1, 0 total2, 0 total3, 0 total4, 0 total5 tipificacao t left bring together chamadas c on c.idtipificacao = t.id c.created_at between '2014-10-13 00:00:00' , '2014-10-19 23:59:59' grouping t.tipificacao1, t.tipificacao2 union select t.tipificacao1, t.tipificacao2, 0 total0, count(c.id) total1, 0 total2, 0 total3, 0 total4, 0 total5 tipificacao t left bring together chamadas c on c.idtipificacao = t.id c.created_at between '2014-10-20 00:00:00' , '2014-10-26 23:59:59' grouping t.tipificacao1, t.tipificacao2 union select t.tipificacao1, t.tipificacao2, 0 total0, 0 total1, count(c.id) total2, 0 total3, 0 total4, 0 total5 tipificacao t left bring together chamadas c on c.idtipificacao = t.id c.created_at between '2014-10-27 00:00:00' , '2014-11-2 23:59:59' grouping t.tipificacao1, t.tipificacao2 union select t.tipificacao1, t.tipificacao2, 0 total0, 0 total1, 0 total2, count(c.id) total3, 0 total4, 0 total5 tipificacao t left bring together chamadas c on c.idtipificacao = t.id c.created_at between '2014-11-3 00:00:00' , '2014-11-9 23:59:59' grouping t.tipificacao1, t.tipificacao2 union select t.tipificacao1, t.tipificacao2, 0 total0, 0 total1, 0 total2, 0 total3, count(c.id) total4, 0 total5 tipificacao t left bring together chamadas c on c.idtipificacao = t.id c.created_at between '2014-11-10 00:00:00' , '2014-11-16 23:59:59' grouping t.tipificacao1, t.tipificacao2 union select t.tipificacao1, t.tipificacao2, 0 total0, 0 total1, 0 total2, 0 total3, 0 total4, count(c.id) total5 tipificacao t left bring together chamadas c on c.idtipificacao = t.id c.created_at between '2014-11-10 00:00:00' , '2014-11-10 23:59:59' grouping t.tipificacao1, t.tipificacao2 returns this: |tipificacao1|tipificacao2|total0|total1|total2|total3|total4|total5| |service |fixed |4 |0 |0 |0 |0 |0 | |service |not fixed |3 |0 |0 |0 |0 |0 | |job in store|fixed |4 |0 |0 |0 |0 |0 | |job in store|not fixed |3 |0 |0 |0 |0 |0 | |service |fixed |0 |5 |0 |0 |0 |0 | |service |not fixed |0 |6 |0 |0 |0 |0 | |job in store|fixed |0 |2 |0 |0 |0 |0 | |job in store|not fixed |0 |1 |0 |0 |0 |0 | |service |fixed |0 |0 |7 |0 |0 |0 | |service |not fixed |0 |0 |8 |0 |0 |0 | |job in store|fixed |0 |0 |4 |0 |0 |0 | |job in store|not fixed |0 |0 |3 |0 |0 |0 | |service |fixed |0 |0 |0 |7 |0 |0 | |service |not fixed |0 |0 |0 |4 |0 |0 | |job in store|fixed |0 |0 |0 |2 |0 |0 | |job in store|not fixed |0 |0 |0 |9 |0 |0 | |service |fixed |0 |0 |0 |0 |1 |0 | |service |not fixed |0 |0 |0 |0 |2 |0 | |job in store|fixed |0 |0 |0 |0 |4 |0 | |job in store|not fixed |0 |0 |0 |0 |7 |0 | |service |fixed |0 |0 |0 |0 |0 |7 | |service |not fixed |0 |0 |0 |0 |0 |7 | |job in store|fixed |0 |0 |0 |0 |0 |4 | |job in store|not fixed |0 |0 |0 |0 |0 |2 | want this: |tipificacao1|tipificacao2|total0|total1|total2|total3|total4|total5| |service |fixed |4 |5 |7 |7 |1 |7 | |service |not fixed |3 |6 |8 |4 |2 |7 | |job in store|fixed |4 |2 |4 |9 |4 |4 | |job in store|not fixed |3 |1 |3 |2 |7 |2 | how can this?

just encapsulate query , grouping 1 time again.

select x.tipificacao1,x.tipificacao2,sum(x.total0),sum(x.total1),sum(x.total2),sum(x.total3),sum(x.total4),sum(x.total5) ( ---that whole big query of yours copypaste here--- ) x grouping x.tipificacao1, x.tipificacao2

edit: , alternative query, test out 1 runs smoother you:

select t.tipificacao1, t.tipificacao2, count(c0.id) total0,count(c1.id) total1,count(c2.id) total2,count(c3.id) total3,count(c4.id) total4,count(c5.id) total5 tipificacao t left bring together chamadas c0 on c0.idtipificacao = t.id , c0.created_at between '2014-10-13 00:00:00' , '2014-10-19 23:59:59' left bring together chamadas c1 on c1.idtipificacao = t.id , c1.created_at between '2014-10-20 00:00:00' , '2014-10-26 23:59:59' left bring together chamadas c2 on c2.idtipificacao = t.id , c2.created_at between '2014-10-27 00:00:00' , '2014-11-2 23:59:59' left bring together chamadas c3 on c3.idtipificacao = t.id , c3.created_at between '2014-11-3 00:00:00' , '2014-11-9 23:59:59' left bring together chamadas c4 on c4.idtipificacao = t.id , c4.created_at between '2014-11-10 00:00:00' , '2014-11-16 23:59:59' left bring together chamadas c5 on c5.idtipificacao = t.id , c5.created_at between '2014-11-10 00:00:00' , '2014-11-10 23:59:59' 1 grouping t.tipificacao1, t.tipificacao2

mysql group-by union

Comments

Post a Comment

Popular posts from this blog

javascript - THREE.js reposition vertices for RingGeometry -

javascript - THREE.js reposition vertices for RingGeometry - codepen available here: http://codepen.io/pehrlich/pen/cogjg i want render ring geometry in scene, , dynamically alter it's arclength (thetalength). start with, i'm calling it's constructor, , recalling code positions vertices. however, causes unusual results. here's method phone call - copied constructor , given new name of setthetalength: three.ringgeometry.prototype.setthetalength = function(thetalength){ // this.thetalength = thetalength; var i, o, uvs = [], radius = this.innerradius, radiusstep = ( ( this.outerradius - this.innerradius ) / this.phisegments ); ( = 0; < this.phisegments + 1; ++ ) { // concentric circles within ring ( o = 0; o < this.thetasegments + 1; o ++ ) { // number of segments per circle var vertex = this.vertices[i + o]; // maybe need query vertex indices here. var segment = this.thetastart + o / this.thetasegments *...
Read more

javascript - I need to update the text of a paragraph by inline edit -

javascript - I need to update the text of a paragraph by inline edit - i've tried this html <div> <h1>some text here</h1> <p>text area text here</p> </div> jquery $(function(){ $('p').on('click', function(e){ e.preventdefault(); var txt = $(this).text(); $(this).parent().append('<textarea>' + txt + '</textarea>'); $(this).remove(); }); }); i need update text of paragraph inline editing. need paragraph new text typed there , remove textarea when click outside of textarea. thanks here update fiddle http://jsfiddle.net/99pxz8et/2/ what need hear on alter event new text area , update it $area.one('focusout', function() { $p.show(); $p.text($area.val()); $area.remove() }); edit: changed .on() .one() javascript jquery
Read more

assembly - What is the addressing mode for ld, add, and rjmp instructions? -

assembly - What is the addressing mode for ld, add, and rjmp instructions? - hey guys know ldi (load immediate) addressing mode set immediate , st instruction addressing mode set index. however, have no thought others curious are! thanks :) the addressing modes explained in instruction set, in first few pages. in table of instructions see operands instruction determine addressing mode. mode indicated arguments assembly instruction. for example, ld had several different ways of operating, depending on arguments. 1 of them ld rd,x d number of register, such ld r3,x . ld r3,-z else (although both indirect addressing). rjmp k relative addressing, jumping pc + k + 1. add rr,rd direct register addressing. result contained in rd. assembly avr instructions
Read more