Group data related to a column

Asked

Viewed 931 times

0

inserir a descrição da imagem aquiI 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 

;

That 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 related to COLUMN OPERATORS

mysql

  • Do you want to do this on the client side (Javascript/jQuery) right? or is it Nodejs? You can put HTML in question?

  • Question became clear but tried group_concat ? http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-Concat

  • added the result as image, I would like to group the data of the first column for the second without repeating, for example EMBRATEL would repeat a single time

  • already tried group_concat but not sure it is not related to 2nda column

1 answer

3


use GROUP_CONCAT to join the information in the same 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’ve helped

  • is that even was not using the group contac properly thank you!

Browser other questions tagged mysql

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