combine several COUNT with JOIN

Question

combine several COUNT with JOIN

Navigation

#1 by (0 votes)

0

I need to calculate the number of victims, processed and crimes, once I have counted these data I need to associate them so that they all appear in a single query, I have the table of victmas , procesados and delitos and all they have in common a variable called id_expedientes , I made this code, but at the time of making the query it marks me as id_expedientes is ambiguous

select Id_expediente,
       count(id_victima) as no_victima,
       count(id_procesado) as no_procesado,
       count(id_delito) as no_delito
from expedientes
left join procesados on procesados.id_expediente=expedientes.Id_expediente
left join victimas on victimas.id_expediente=expedientes.Id_expediente
left join delitos on delitos.Id_expediete=expedientes.Id_expediente
group by 1 ;

asked by Erika Mellark Gonzalez 26.08.2018 в 02:09

source

1 answer

Upload my web app to Heroku without having created it using git Send within a modal 4 different values in a radio input

score 0 · Accepted Answer

Since Id_expediente is common to all tables, when you only reference the column without an alias or table name, the engine does not know or can not decide which table will be the column, in your case you just have to indicate some (any of the tables)

select expedientes.Id_expediente,
       ...

Either way it is recommended that you use the aliases of tables that allow you to write a little less and avoid these types of problems, for example:

select e.Id_expediente,
       count(v.id_victima) as no_victima,
       count(p.id_procesado) as no_procesado,
       count(d.id_delito) as no_delito
       from expedientes e
       left join procesados p
            on p.id_expediente=e.Id_expediente
       left join victimas v
            on v.id_expediente=e.Id_expediente
       left join delitos d
            on d.Id_expediente=e.Id_expediente
       group by e.Id_expediente;

The letters e , p , v , d we use as alias of each table and instead of using the full name of the table we use this alias.