Avoid duplicate records and account for them

Question

Avoid duplicate records and account for them

Navigation

#1 by (2 votes)

2

I have a query in which I get a list of companies

SELECT S.FileB64 AS ImgB64, E.* FROM dbempresas E
    LEFT JOIN systorage S ON S.id = E.Logo_100 
    ORDER BY E.Nombre;

RESULTADO:
id |     RFC      |           Nombre
---+--------------+----------------------------------
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC
15 | BBB010101BBB | CORPORACION ACME
16 | CCC010101CCC | INDUSTRIAS KORD

So far so good, but I want to add how many branches there are per company , throwing me a result like this:

id |     RFC      |           Nombre                    |  Sucursales
---+--------------+-------------------------------------+--------------
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC    |    2
15 | BBB010101BBB | CORPORACION ACME                    |    1
16 | CCC010101CCC | INDUSTRIAS KORD                     |    1

To try to achieve the above I have done this:

SELECT S.FileB64 AS ImgB64, ES.id, E.* FROM dbempresas E
    LEFT JOIN systorage S ON S.id = E.Logo_100 
    LEFT JOIN dbempresas_sucursales ES ON ES.IdEmpresa = E.id
    ORDER BY E.Nombre;

RESULTADO:
id |     RFC      |           Nombre                    |  ES.id
---+--------------+-------------------------------------+--------------
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC    |    13
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC    |    16
15 | BBB010101BBB | CORPORACION ACME                    |    17
16 | CCC010101CCC | INDUSTRIAS KORD                     |    18

I know that with COUNT() I can count the records but I have no idea how to achieve it in the same query. How can I avoid repeating the id of the company and giving me the result I want?

These are the tables in which I consult:

+------------------------------+   +-------------------------------+
|          dbEmpresas          |   |     dbEmpresas_Sucursales     |
+-------------+----------------+   +-------------+-----------------+
|  id         |  bigint(20)    |   |  id         |  bigint(20)     |
|  RFC        |  varchar(15)   |   |  IdEmpresa  |  bigint(20)     |
|  Nombre     |  varchar(100)  |   |  Nombre     |  varchar(100)   |
|  Logo_100   |  bigint(20)    |   +-------------+-----------------+
+-------------+----------------+

+-----------------------------+
|          syStorage          | 
+-----------+-----------------+
|  id       |  bigint(20)     |
|  FileB64  |  text           |
+-----------+-----------------+

mysql

asked by JuankGlezz 14.03.2017 в 17:11

source

1 answer

Function to search in a chain the commas and replace them by point Problem with menu in python

score 2 · Accepted Answer

The only thing you really need is to do a GROUP BY for all the columns that do not come from the dbEmpresas_Sucursales table. With that, now you can use the function COUNT correctly and without erroneous results.

SELECT S.FileB64 AS ImgB64, E.*, COUNT(ES.id) AS Sucursales
FROM dbempresas E
LEFT JOIN systorage S ON S.id = E.Logo_100 
LEFT JOIN dbempresas_sucursales ES ON ES.IdEmpresa = E.id
GROUP BY E.id, E.RFC, E.Nombre, E.Logo_100, S.FileB64
ORDER BY E.Nombre;