Group two fields in the same table with mysql

Asked

Viewed 2,986 times

2

I’m trying to put together a query, where I can print out the broker’s name and the sales percentage for it. When grouped by name1, the field name_broker2 does not group and vice versa, when grouped both is not carried out the grouping.

Data Table

Tabela Dados_venda

Query grouping by name1

SELECT 
    v.nome_corretor1, v.nome_corretor2,
    ( COUNT(v.nome_corretor1)/ ( SELECT count(vt.idDados_venda) FROM Dados_venda vt) ) * 100 as Pcorretor1,
    ( COUNT(v.nome_corretor2)/ ( SELECT count(vt.idDados_venda) FROM Dados_venda vt) ) * 100 as Pcorretor2
FROM 
    Dados_venda v
GROUP BY
     v.nome_corretor1

Upshot Query agrupando por nome_corretor1

Query grouping by name2

SELECT 
    v.nome_corretor1, v.nome_corretor2,
    ( COUNT(v.nome_corretor1)/ ( SELECT count(vt.idDados_venda) FROM Dados_venda vt) ) * 100 as Pcorretor1,
    ( COUNT(v.nome_corretor2)/ ( SELECT count(vt.idDados_venda) FROM Dados_venda vt) ) * 100 as Pcorretor2
FROM 
    Dados_venda v
GROUP BY
     v.nome_corretor2

Upshot inserir a descrição da imagem aqui

Query grouping by name1 and name2

SELECT 
    v.nome_corretor1, v.nome_corretor2,
    ( COUNT(v.nome_corretor1)/ ( SELECT count(vt.idDados_venda) FROM Dados_venda vt) ) * 100 as Pcorretor1,
    ( COUNT(v.nome_corretor2)/ ( SELECT count(vt.idDados_venda) FROM Dados_venda vt) ) * 100 as Pcorretor2
FROM 
    Dados_venda v
GROUP BY
     v.nome_corretor1,
v.nome_corretor2

Upshot

inserir a descrição da imagem aqui

mysql group-by

  • you have two brokers per sale?

  • @Fleuquerlima, in a sale can happen to have more than one broker involved, in partnership cases for example.

  • It’s not better in the clause FROM make 2 selects on top of the Dados_venda(one for each broker) and then make Join?

  • @DH, could you illustrate? I didn’t understand what you meant, it is possible to make a Join in the same table:

  • Isn’t it better to have a BROKER table, a SALE and a third Salesbroker? Where do you link all who participate in this sale? Since a sale can have more than one broker and a broker participates in more than one sale, this would be a case of many to many, would generate a new table - this would be a better practice and easier for you to relate.

  • Looking over this table does not seem to follow the Normalization rules; Perhaps it would be interesting for you to check them.

  • Something like that SELECT ... FROM Dados_venda C1, Dados_venda C2, and there would be one pulling information from broker 1 and the other from 2, but looking at this table from above, she has no PK?

  • @DH. It has PK key but not of the brokers, it relates to Dados_client, Dados_immovel

  • The first two querys should return error , in your group by, there is something wrong there.

  • @Fleuquerlima. Now there are separate tables as you mentioned, the broker table in the sale table a FK, even though it is not grouping. SELECT c.broker name1, c.broker name2, COUNT(c.brokername1) as Vcorretor1, COUNT(c.brokername2) as Vcorretor2 FROM N Data_broker c GROUP BY c.broker_name1, c.brokername2

  • I entered the solution, now it worked normally as expected.

Show 6 more comments

1 answer

0


The solution found at the time 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

Upshot

inserir a descrição da imagem aqui

Browser other questions tagged mysql group-by

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