Do a Count for every row inside an inner join-CodePudding

I have a few tables with data from requests in a webpage. The thing is, I did some inner joins to merge data from the foreign keys in the requests table, but I want to add a column that counts the respective equipment that the office that sent the request has in total.

Here is my code for a better understanding of what I'm asking for.

My query is this:

SELECT realiza_soli.*, oficina.id_lugar, oficina.desc_lugar,
tipo_equipamiento.nom_tipo FROM realiza_soli
INNER JOIN usuario
ON realiza_soli.fk_user = usuario.id_user
INNER JOIN tipo_equipamiento
ON realiza_soli.fk_tipo = tipo_equipamiento.id_tipo
INNER JOIN oficina
ON usuario.fk_ofic = oficina.id_lugar
AND realiza_soli.estado_soli = 'Pendiente Info'
ORDER BY realiza_soli.id_soli ASC;

And the table generated is this:

 --------- ------------- ---------------------- ---------------- ---------------- ---------------- --------- --------- ---------- ------------ ---------- 
| id_soli | titulo_soli | desc_soli            | estado_soli    | fecha_ini_soli | fecha_fin_soli | fk_tipo | fk_user | id_lugar | desc_lugar | nom_tipo |
 --------- ------------- ---------------------- ---------------- ---------------- ---------------- --------- --------- ---------- ------------ ---------- 
|       1 | Solicitud 1 | Descripción          | Pendiente Info | 2020-01-01     | NULL           |       1 |       4 |       10 | Oficina 10 | Monitor  |
|      13 | 25 Monitor  | Dame mmmmmmmaaaaaaas | Pendiente Info | 2021-11-05     | NULL           |       1 |       5 |        5 | Oficina 5  | Monitor  |
 --------- ------------- ---------------------- ---------------- ---------------- ---------------- --------- --------- ---------- ------------ ----------

The thing is, I want to add a count to every row, based on the 'oficina' table, count how many equipment it has, from the table 'equipamiento'.

Here is my 'equipamiento' table:

 ---------- ------------ ---------- ----------------- ------------- ------------ ------------- -------------- ----------- --------- ---------- --------- --------- 
| id_equip | fecha_adq  | garantia | desc_equip      | marca_equip | tipo       | lugar_equip | estado_equip | fk_estado | fk_tipo | fk_marca | fk_ofic | fk_prov |
 ---------- ------------ ---------- ----------------- ------------- ------------ ------------- -------------- ----------- --------- ---------- --------- --------- 
|        1 | 2020-01-01 |       24 | Teclado         | Kolke       | Componente | Oficina 5   | Instalado    |         4 |      21 |        3 |       5 |       1 |
|        2 | 2020-02-02 |       12 | Tarjeta Gráfica | NVIDIA      | Componente | Oficina 3   | Instalado    |         4 |       6 |        7 |       3 |       2 |
|        3 | 2020-03-03 |        9 | Memoria RAM     | ADATA       | Componente | NULL        | Stock        |         2 |       7 |       26 |    NULL |    NULL |
|        4 | 2020-04-04 |       10 | Tarjeta de Red  | Realtek     | Componente | Oficina 2   | Instalado    |         4 |       5 |       20 |       2 |       3 |
|        5 | 2020-01-01 |       12 | Monitor         | Samsung     | Componente | Oficina 5   | Instalado    |         4 |       1 |       22 |       5 |       3 |
 ---------- ------------ ---------- ----------------- ------------- ------------ ------------- -------------- ----------- --------- ---------- --------- ---------

As you can see, the 'equipamiento' table has 'fk_ofic' column, that references to 'oficina' table:

 ---------- ------------ ------------- ----------------- -------------- -------------- ----------- -------- ---------- 
| id_lugar | desc_lugar | grupo_lugar | dir_lugar       | depart_lugar | ciudad_lugar | tel_lugar | fk_dep | fk_grupo |
 ---------- ------------ ------------- ----------------- -------------- -------------- ----------- -------- ---------- 
|        1 | Oficina 1  | Oficina     | Calle Random 1  | Montevideo   | Montevideo   |  94132471 |      1 |        5 |
|        2 | Oficina 2  | Oficina     | Calle Random 2  | Montevideo   | Montevideo   |  91356985 |      1 |        5 |
|        3 | Oficina 3  | Oficina     | Calle Random 3  | Montevideo   | Montevideo   |  92358985 |      1 |        5 |
|        4 | Oficina 4  | Oficina     | Calle Random 4  | Montevideo   | Montevideo   |  95355685 |      1 |        5 |
|        5 | Oficina 5  | Oficina     | Calle Random 5  | Montevideo   | Montevideo   |  97356990 |      1 |        5 |
|        6 | Oficina 6  | Oficina     | Calle Random 6  | Montevideo   | Montevideo   |  99344985 |      1 |        5 |
|        7 | Oficina 7  | Oficina     | Calle Random 7  | Montevideo   | Montevideo   |  91234567 |      1 |        5 |
|        8 | Oficina 8  | Director    | Calle Random 8  | Montevideo   | Montevideo   |  91234567 |      1 |        1 |
|        9 | Oficina 9  | Informática | Calle Random 9  | Montevideo   | Montevideo   |  91234567 |      1 |        2 |
|       10 | Oficina 10 | SubB        | Calle Random 10 | Montevideo   | Montevideo   |  91234567 |      1 |        4 |
|       11 | Oficina 11 | Compras     | Calle Random 11 | Montevideo   | Montevideo   |  91234567 |      1 |        6 |
|       12 | Oficina 12 | Compras     | Calle Random 12 | Montevideo   | Montevideo   |  91234567 |      1 |        6 |
 ---------- ------------ ------------- ----------------- -------------- -------------- ----------- -------- ----------

If I do an inner join between those two tables u can see what equipment is installed in each office:

 ---------- ----------------- ---------- ------------ 
| id_equip | desc_equip      | id_lugar | desc_lugar |
 ---------- ----------------- ---------- ------------ 
|        1 | Teclado         |        5 | Oficina 5  |
|        2 | Tarjeta Gráfica |        3 | Oficina 3  |
|        4 | Tarjeta de Red  |        2 | Oficina 2  |
|        5 | Monitor         |        5 | Oficina 5  |
 ---------- ----------------- ---------- ------------

So, two components are installed in one office, office 5, and one in ofice 2 and one in ofice 3. Based on this information, I want to add a column of total_equipment for each office. Here is the wanted result:

 --------- ------------- ---------------------- ---------------- ---------------- ---------------- --------- --------- ---------- ------------ ---------- -------------- 
| id_soli | titulo_soli | desc_soli            | estado_soli    | fecha_ini_soli | fecha_fin_soli | fk_tipo | fk_user | id_lugar | desc_lugar | nom_tipo | total_equip |
 --------- ------------- ---------------------- ---------------- ---------------- ---------------- --------- --------- ---------- ------------ ---------- -------------- 
|       1 | Solicitud 1 | Descripción          | Pendiente Info | 2020-01-01     | NULL           |       1 |       4 |       10 | Oficina 10 | Monitor  |           0 |
|      13 | 25 Monitor  | Dame mmmmmmmaaaaaaas | Pendiente Info | 2021-11-05     | NULL           |       1 |       5 |        5 | Oficina 5  | Monitor  |           2 |
 --------- ------------- ---------------------- ---------------- ---------------- ---------------- --------- --------- ---------- ------------ ---------- --------------

That's because the first request belongs to office 10, that has 0 equipment installed, and the second request belongs to office 5 that has 2 equipment installed.

Sorry if my explanation is awful, but I can't explain it in another way. Hope anyone can help. If u have any question about my code I can explain.

CodePudding user response：

When I understand your description correct, this should work:

SELECT 
   realiza_soli.*, 
   oficina.id_lugar, 
   oficina.desc_lugar,
   tipo_equipamiento.nom_tipo,
   (SELECT COUNT(*) FROM equipamiento WHERE equipamiento.fk_ofic = oficina.id_lugar) as total_equip
FROM realiza_soli
INNER JOIN usuario ON realiza_soli.fk_user = usuario.id_user
INNER JOIN tipo_equipamiento ON realiza_soli.fk_tipo = tipo_equipamiento.id_tipo
INNER JOIN oficina ON usuario.fk_ofic = oficina.id_lugar
                  AND realiza_soli.estado_soli = 'Pendiente Info'
ORDER BY realiza_soli.id_soli ASC;