Group data related to a column

Question

Group data related to a column

Navigation

#1 by (2 votes)

0

I have the following code

    SELECT 
t2.conta_nome as operacao, t1.provedor_nome as operadora,
perfil, 
count(*) as total 

FROM asteriskcdrdb.cdr_tarifacao a 

INNER JOIN telefonia.provedor t1 ON a.operadora = t1.provedor_id 
INNER JOIN telefonia.conta t2 ON a.accountcode = t2.conta_id 
WHERE calldate >= Date_add(now(), interval -60 second)
GROUP by  operacao,operadora
ORDER by operadora 
LIMIT 50 

;

What it generates:

Operations | Operators

GFK OI | ALGAR

BMG BANK | EMBRATEL

IESDE | EMBRATEL

MONTREAL | EMBRATEL

RENAULT | EMBRATEL

BMG BANK | GVT

MONTREAL | GVT

I would like to know how to group all values highlighted to COLUMN OPERATORS

mysql

asked by anonymous 22.11.2014 / 15:10

1 answer

getimagesize () does not work in Plesk + Windows How do I get the main color of an ImageView?

score 2 · Accepted Answer

Use GROUP_CONCAT to merge information into one column. See the example below:

SELECT
   GROUP_CONCAT(cep.strcep) AS CEP,
   cidade.strnome
FROM Cidade
INNER JOIN cep ON cep.intcidadeid = cidade.intcidadeid
GROUP BY (cidade.strnome)

Your select should look like this:

SELECT
   GROUP_CONCAT(t2.conta_nome) AS operacao,
   t1.provedor_nome as operadora
FROM asteriskcdrdb.cdr_tarifacao a
INNER JOIN telefonia.provedor t1 ON a.operadora = t1.provedor_id
INNER JOIN telefonia.conta t2 ON a.accountcode = t2.conta_id
WHERE calldate >= Date_add(now(), interval -60 second)
GROUP by  operadora
LIMIT 50;

I hope I have helped