count data from one table with parameters from another

Question

count data from one table with parameters from another

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

It may not be very clear in the question but I hope to make it clear by the example I am exposing.

I have three tables from which I need to take an account

table A

|ciudad_cod |correlativo    |fecha      |estado_cod |detalle   |
|-----------|---------------|-----------|-----------|----------|
|C1011      | 1             |20-01-2018 |E001       |DETALLE 1 | 
|C1011      | 2             |22-03-2018 |E002       |DETALLE 2 | 
|C1012      | 3             |09-05-2018 |E002       |DETALLE 3 |
|C1012      | 4             |01-06-2018 |E002       |DETALLE 4 |
|C1012      | 5             |21-06-2018 |E003       |DETALLE 5 |
|C1013      | 6             |05-08-2018 |E004       |DETALLE 6 |

table B

|ciudad_cod |ciudad     |
|-----------|-----------|
|C1011      |santiago   |
|C1012      |concepcion |
|C1013      |iquique    |

table C

|estado_cod |estado     |
|-----------|-----------|
|E001       |activo     |
|E002       |pendiente  |
|E003       |traspaso   |
|E004       |cerrado    |

and as a result I want a table like this

|ciudad     |activo |pendiente  |traspaso   |cerrado    |
|-----------|-------|-----------|-----------|-----------|
|santiago   |   1   |   1       |0          |0          |
|concepcion |   0   |   2       |1          |0          |
|iquique    |   0   |   0       |0          |1          |

I need to count the data in table A but with the parameters of table B and table C

I hope you understand thanks

mysql

asked by Ariel Ayala 03.10.2018 в 14:32

source

2 answers

JAVA How to get a third list based on two lists without a certain length? variable with jquery html content

score 1 · Answer 1

select 
ciudad_cod,
ciudad, 
(select count(1) from tablaA where tablaA.ciudad_cod=tabla b.ciudad_cod and estado_cod='E001') activo,
(select count(1) from tablaA where tablaA.ciudad_cod=tabla b.ciudad_cod and estado_cod='E002') pendiente,
(select count(1) from tablaA where tablaA.ciudad_cod=tabla b.ciudad_cod and estado_cod='E003') traspaso,
(select count(1) from tablaA where tablaA.ciudad_cod=tabla b.ciudad_cod and estado_cod='E004') cerrado
from tabla b

score 1 · Answer 2

It works for me exactly how you want your result like this:

SELECT
    tablaB.ciudad,
    Count( IF ( tablaa.estado_cod = 'E001', 1, NULL ) ) AS activo,
    Count( IF ( tablaa.estado_cod = 'E002', 1, NULL ) ) AS pendiente,
    Count( IF ( tablaa.estado_cod = 'E003', 1, NULL ) ) AS traspaso,
    Count( IF ( tablaa.estado_cod = 'E004', 1, NULL ) ) AS cerrado 
FROM
    tablaa AS tablaA
    INNER JOIN tablab AS tablaB ON tablaA.ciudad_cod = tablaB.ciudad_cod
GROUP BY tablaB.ciudad