Group two fields in the same table with mysql

Navigation
2

I'm trying to set up a query, where I can print out the broker's name and the percentage of the sale pertaining to it. When grouping by name_corretor1, the field name_corretor2 does not group and vice versa, when grouping both the grouping is not performed.

SalesData table

Querygroupingbyname_rector1

SELECTv.nome_corretor1,v.nome_corretor2,(COUNT(v.nome_corretor1)/(SELECTcount(vt.idDados_venda)FROMDados_vendavt))*100asPcorretor1,(COUNT(v.nome_corretor2)/(SELECTcount(vt.idDados_venda)FROMDados_vendavt))*100asPcorretor2FROMDados_vendavGROUPBYv.nome_corretor1

Result

Querygroupingbyname_rector2

SELECTv.nome_corretor1,v.nome_corretor2,(COUNT(v.nome_corretor1)/(SELECTcount(vt.idDados_venda)FROMDados_vendavt))*100asPcorretor1,(COUNT(v.nome_corretor2)/(SELECTcount(vt.idDados_venda)FROMDados_vendavt))*100asPcorretor2FROMDados_vendavGROUPBYv.nome_corretor2

Result

Querygroupingbyname_rector1andname_rector2

SELECTv.nome_corretor1,v.nome_corretor2,(COUNT(v.nome_corretor1)/(SELECTcount(vt.idDados_venda)FROMDados_vendavt))*100asPcorretor1,(COUNT(v.nome_corretor2)/(SELECTcount(vt.idDados_venda)FROMDados_vendavt))*100asPcorretor2FROMDados_vendavGROUPBYv.nome_corretor1,v.nome_corretor2

Result

    
asked by anonymous 08.04.2016 / 18:56

1 answer

0

The solution found at the moment was the union of the fields through two querys: 

SELECT 
    c.nome_corretor1 as corretor,
    ( COUNT(c.nome_corretor1)/ ( SELECT count(vt.idDados_corretor) FROM Dados_corretor vt) ) * 100 as percentual
FROM 
    Dados_corretor c
GROUP BY
    c.nome_corretor1
UNION

SELECT 
    c2.nome_corretor2,

    ( COUNT(c2.nome_corretor2)/ ( SELECT count(vt2.idDados_corretor) FROM Dados_corretor vt2) ) * 100 as Pcorretor2
FROM 
    Dados_corretor c2
GROUP BY
 c2.nome_corretor2

Result

    
10.04.2016 / 23:19
Working with Parallel using the NInject library How to Create an Event in Google Analytics