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

Asked

Viewed 1,373 times

1

I am making a select for a report of calls per carrier and each column of amount, I have a composite select 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 on each line with the total, without sorting by date.

mysql

  • You have other possible values in dstchannel?

  • Yes, I do. In this case, I want to take only the 5 operators. If I take each subselect and do the separate query, it will return the correct result. But I need this to be in the same select sepadado only by column, got it?

  • If each operator came on a different line, it doesn’t suit you?

  • Anyway, the problem is in your GROUP BY.

  • I think not, because each column represents an operator. If I only have the date and quantity, how will I know whose is what? I imagine it’s in group by, but I don’t know how I do it.

2 answers

3

If you accept that each carrier comes in a row, and not in a column, you can just do so:

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)

  • In mysql it would work to replace REGEXP for IN ('claro', 'tim', 'vivo', 'oi', 'nextel') ?

  • 1

    Yes, if the column value is exactly one of these (because regexp will marry "Clear Operator" and things like).

  • I have to have column separa pq that values will be implemented in a highcharts. I can’t work with in pq the carrier value is type 'SIP/712_NEXTEL1-000000b3'. That’s why I use like. @bfavaretto Your select has not grouped. Thanks.

3


Use sum(), for example:

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)

Upshot:

inserir a descrição da imagem aqui

All table:

inserir a descrição da imagem aqui

  • It worked @Jader. Thanks.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.