How to do a select with dynamic columns for each like?

Navigation
1

I'm doing a select for a report of calls by carrier and each column of quantity, I have a select compound and that should return me the amount of calls from each carrier. 

SELECT date(calldate) as 'Data',
(select count(*) FROM cdr where dstchannel like '%claro%' and (calldate between '2014-08-01' and '2014-08-11' ) ) as 'Claro',
(select count(*) FROM cdr where dstchannel like '%tim%' and (calldate between '2014-08-01' and '2014-08-11' ) ) as 'Tim',
(select count(*) FROM cdr where dstchannel like '%vivo%' and (calldate between '2014-08-01' and '2014-08-11' ) ) as 'Vivo',
(select count(*) FROM cdr where dstchannel like '%oi%' and (calldate between '2014-08-01' and '2014-08-11' ) ) as 'Oi',
(select count(*) FROM cdr where dstchannel like '%nextel%' and(calldate between '2014-08-01' and '2014-08-11' ) ) as 'Nextel'
FROM cdr where
(dstchannel regexp 'claro|Tim|vivo|oi|nextel')
and (calldate between '2014-08-01' and '2014-08-11' ) group by date(calldate)

The result that is returned is the same value in each row with the total, without separating by date.

    
asked by anonymous 11.08.2014 / 19:32

2 answers

3

Use sum (), eg: 

SELECT date( calldate ) AS 'Data', sum( dstchannel LIKE '%claro%' ) claro, sum( dstchannel LIKE '%oi%' ) oi
FROM cdr where
(dstchannel regexp 'claro|Tim|vivo|oi|nextel')
and (calldate between '2014-08-01' and '2014-08-11' ) group by date(calldate)

Result:

Entiretable:

    
11.08.2014 / 20:39
3

If you accept that each carrier comes in a line, not in a column, you can simply do this: 

SELECT 
    calldate AS Data,
    dstchannel AS Operadora,
    COUNT(*) AS Quantiade
FROM cdr 
WHERE dstchannel REGEXP 'claro|tim|vivo|oi|nextel'
    AND calldate BETWEEN '2014-08-01' AND '2014-08-11' 
GROUP BY dstchannel, YEAR(calldate), MONTH(calldate), DAY(calldate)
ORDER BY dstchannel, YEAR(calldate), MONTH(calldate), DAY(calldate)
    
11.08.2014 / 20:22
Zero left inside integer variable [duplicate] How do I find the right source of an event in a complex object via JavaScript?