CODEWITHSUNDEEP
mysqlgroup-byunion
SlimBoy
SlimBoy

Reputation: 421

MySQL: Union with many Count and Group By

SELECT t.tipificacao1, t.tipificacao2, count(c.id) as Total0, 0 as Total1, 0 as Total2, 0 as Total3, 0 as Total4, 0 as Total5
FROM tipificacao as t 
left JOIN chamadas as c ON c.idTipificacao = t.id 
where c.created_at BETWEEN '2014-10-13 00:00:00' AND '2014-10-19 23:59:59' 
GROUP BY t.tipificacao1, t.tipificacao2
UNION
SELECT t.tipificacao1, t.tipificacao2, 0 as Total0, count(c.id) as Total1, 0 as Total2, 0 as Total3, 0 as Total4, 0 as Total5
FROM tipificacao as t 
left JOIN chamadas as c ON c.idTipificacao = t.id 
where c.created_at BETWEEN '2014-10-20 00:00:00' AND '2014-10-26 23:59:59' 
GROUP BY t.tipificacao1, t.tipificacao2
UNION
SELECT t.tipificacao1, t.tipificacao2, 0 as Total0, 0 as Total1, count(c.id) as Total2, 0 as Total3, 0 as Total4, 0 as Total5
FROM tipificacao as t 
left JOIN chamadas as c ON c.idTipificacao = t.id 
where c.created_at BETWEEN '2014-10-27 00:00:00' AND '2014-11-2 23:59:59' 
GROUP BY t.tipificacao1, t.tipificacao2
UNION
SELECT t.tipificacao1, t.tipificacao2, 0 as Total0, 0 as Total1, 0 as Total2, count(c.id) as Total3, 0 as Total4, 0 as Total5
FROM tipificacao as t 
left JOIN chamadas as c ON c.idTipificacao = t.id 
where c.created_at BETWEEN '2014-11-3 00:00:00' AND '2014-11-9 23:59:59' 
GROUP BY t.tipificacao1, t.tipificacao2
UNION
SELECT t.tipificacao1, t.tipificacao2, 0 as Total0, 0 as Total1, 0 as Total2, 0 as Total3, count(c.id) as Total4, 0 as Total5
FROM tipificacao as t 
left JOIN chamadas as c ON c.idTipificacao = t.id 
where c.created_at BETWEEN '2014-11-10 00:00:00' AND '2014-11-16 23:59:59' 
GROUP BY t.tipificacao1, t.tipificacao2
UNION
SELECT t.tipificacao1, t.tipificacao2, 0 as Total0, 0 as Total1, 0 as Total2, 0 as Total3, 0 as Total4, count(c.id) as Total5
FROM tipificacao as t 
left JOIN chamadas as c ON c.idTipificacao = t.id 
where c.created_at BETWEEN '2014-11-10 00:00:00' AND '2014-11-10 23:59:59' 
GROUP BY t.tipificacao1, t.tipificacao2
It 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     |
What I want is 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 I do this?

Upvotes: 0

Views: 96

Answers (1)

David162795
David162795

Reputation: 1866

just encapsulate the query and group it once again. 

SELECT x.tipificacao1,x.tipificacao2,sum(x.Total0),sum(x.Total1),sum(x.Total2),sum(x.Total3),sum(x.Total4),sum(x.Total5)
FROM
(

   ---THAT WHOLE BIG QUERY OF YOURS COPYPASTE HERE---

) x
GROUP BY x.tipificacao1, x.tipificacao2

edit: And an alternative query, test out which one runs smoother for you:

SELECT t.tipificacao1, t.tipificacao2,
count(c0.id) as Total0,count(c1.id) as Total1,count(c2.id) as Total2,count(c3.id) as Total3,count(c4.id) as Total4,count(c5.id) as Total5

FROM tipificacao as t 
left JOIN chamadas as c0 ON c0.idTipificacao = t.id AND c0.created_at BETWEEN '2014-10-13 00:00:00' AND '2014-10-19 23:59:59'
left JOIN chamadas as c1 ON c1.idTipificacao = t.id and c1.created_at BETWEEN '2014-10-20 00:00:00' AND '2014-10-26 23:59:59' 
left JOIN chamadas as c2 ON c2.idTipificacao = t.id and c2.created_at BETWEEN '2014-10-27 00:00:00' AND '2014-11-2 23:59:59' 
left JOIN chamadas as c3 ON c3.idTipificacao = t.id and c3.created_at BETWEEN '2014-11-3 00:00:00' AND '2014-11-9 23:59:59'
left JOIN chamadas as c4 ON c4.idTipificacao = t.id and c4.created_at BETWEEN '2014-11-10 00:00:00' AND '2014-11-16 23:59:59' 
left JOIN chamadas as c5 ON c5.idTipificacao = t.id and c5.created_at BETWEEN '2014-11-10 00:00:00' AND '2014-11-10 23:59:59' 

WHERE 1

GROUP BY t.tipificacao1, t.tipificacao2

Upvotes: 1

Related Questions