[SOLUCIONADO] Restar dos Count MySQL

[guilens2]

Buenos días, he estado intentando hacer una consulta mysql durante varias horas pero no hay manera, a ver si pueden ayudarme por favor.

Tengo la tabla Facturas, la tabla Abonos y la Tabla Clientes.

La Relación de Facturas y Abonos se hace gracias al id del Cliente.

Necesito conseguir el resultado de restar el total de Facturas realizadas - el Total de Abonos realizados por los clientes.

Consigo hacerlo de forma individual, es decir, para un cliente en concreto, con la siguiente consulta

Código MySQL:

SELECT (SELECT COUNT(distinct factura_numero) FROM Facturas
 INNER JOIN Ventas ON ventas_id = factura_ventas_id
 INNER JOIN Clientes ON cliente_id=ventas_id_cliente
# ----------------------------------------------
WHERE cliente_id=121) -
# ----------------------------------------------
(SELECT COUNT(abonos_id) FROM Abonos
 INNER JOIN Clientes ON cliente_id=abonos_id_cliente
# ----------------------------------------------
WHERE cliente_id=121
# ----------------------------------------------
) AS RestaCount
 

Con esto consigo restar 23 facturas - 6 abonos para id 121 = 17. Hasta aquí correcto.

Lo ideal sería conseguirlo para todos los clientes, algo así como un group by clientes_id y obtener:
id 121, total 23
id 122, total 12
.....

Imagino que estoy haciendo algo mal, porque algunos clientes No han hecho abonos e incluso algunos clientes No han llegado a facturar todavía, entonces tal vez haya que meter un Left Join en algún sitio, pero no se donde.

Si introduzco un group by haciendo algo así:

Código MySQL:

SELECT t1.Total - t2.Total FROM(
SELECT cliente_id, COUNT( DISTINCT factura_numero ) as Total 
FROM Facturas
INNER JOIN Ventas ON ventas_id = factura_ventas_id
INNER JOIN Clientes ON cliente_id = ventas_id_cliente
GROUP BY cliente_id) as t1, (SELECT cliente_id, COUNT(abonos_id) as Total 
        FROM Abonos
       INNER JOIN Clientes ON cliente_id=abonos_id_cliente group by cliente_id) as t2 WHERE t1.cliente_id=t2.cliente_id
 

Sencillamente se vuelve loco,

Un saludo y gracias

[leonardo_josue]

Hola guilens2:

El problema que tienes con tu consulta es que tienes dos relaciones 1 a muchos, que al unirse se convierten en muchos a muchos, lo cual puede ocasionar varios dolores de cabeza.

Tienes varias formas de afrontar esta consulta, una de las más simples desde mi punto de vista es Consolidar los totales en facturas y abonos, de tal manera que tu relación cambie de 1 a 1, no colocas mucha información de tus tablas, pero tratando de hacerle al adivino, creo que tienes algo como esto:

Código MySQL:

mysql> SELECT * FROM clientes;
+------------+-------------+
| id_cliente | descripcion |
+------------+-------------+
|          1 | Cliente 1   |
|          2 | Cliente 2   |
|          3 | Cliente 3   |
|          4 | Cliente 4   |
+------------+-------------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM facturas;
+------------+------------+-------------+-------------+
| id_factura | id_cliente | num_factura | descripcion |
+------------+------------+-------------+-------------+
|          1 |          1 |           1 | Factura 1.1 |
|          2 |          1 |           2 | Factura 1.2 |
|          3 |          2 |           3 | Factura 2.1 |
|          4 |          2 |           4 | Factura 2.1 |
|          5 |          3 |           5 | Factura 3.1 |
+------------+------------+-------------+-------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM abonos;
+----------+------------+-----------+-------------+
| id_abono | id_cliente | num_abono | descripcion |
+----------+------------+-----------+-------------+
|        1 |          1 |        10 | Abono 1.1   |
|        2 |          1 |        11 | Abono 1.2   |
|        3 |          2 |        12 | Abono 2.1   |
+----------+------------+-----------+-------------+
3 rows in set (0.00 sec)
 

es decir, hay clientes que tienen facturas y abonos, clientes que solo tienen facturas y clientes que no tienen ni facturas ni abonos...

Si observas las tablas de FACTURAS Y ABONOS, puedes CONSOLIDAR los totales, agrupando por ID del cliente, es decir, hacer algo como esto:

Código MySQL:

mysql> SELECT id_cliente, COUNT(*) total_facturas
    -> FROM facturas
    -> GROUP BY id_cliente;
+------------+----------------+
| id_cliente | total_facturas |
+------------+----------------+
|          1 |              2 |
|          2 |              2 |
|          3 |              1 |
+------------+----------------+
3 rows in set (0.00 sec)
 

Observa que ahora puedes unir tu tabla CLIENTES con esta tabla, en una relación 1 a 1... lo mismo ocurre con los abonos.

Ahora bien, aquí es donde entran los LEFT JOIN's, para considerar aquellos que no tienen elementos en alguna de las tablas (o en ninguna), sería más o menos así:

Código MySQL:

mysql> SELECT C.id_cliente, C.descripcion, F.total_facturas, A.total_abonos
    -> FROM clientes C
    -> LEFT JOIN ( SELECT id_cliente, COUNT(*) total_facturas
    ->             FROM facturas
    ->             GROUP BY id_cliente) F ON C.id_cliente = F.id_cliente
    -> LEFT JOIN ( SELECT id_cliente, COUNT(*) total_abonos
    ->             FROM abonos
    ->             GROUP BY id_cliente) A ON C.id_cliente = A.id_cliente ;
+------------+-------------+----------------+--------------+
| id_cliente | descripcion | total_facturas | total_abonos |
+------------+-------------+----------------+--------------+
|          1 | Cliente 1   |              2 |            2 |
|          2 | Cliente 2   |              2 |            1 |
|          3 | Cliente 3   |              1 |         NULL |
|          4 | Cliente 4   |           NULL |         NULL |
+------------+-------------+----------------+--------------+
4 rows in set (0.00 sec)
 

Observa que los LEFT JOIN's se hacen SOBRE LAS CONSULTAS CONSOLIDADAS, por lo que no debes tener problemas para hacer la resta.

Dale un vistazo para ver si te sirve.

Saludos
Leo.

[guilens2]

Hola Leo!,

Gracias por tu respuesta, se ve que le has dedicado tiempo, perdona por no haberte dado la información completamente detallada, soy novato hasta para preguntar, jejeje.

Todo lo que has estado explicando lo entiendo ahora mejor, tienes razón y a su vez, no sabía como enlazar los left join en la consulta.

No soy un experto en bases de datos que digamos...voy sobreviviendo como puedo.

Voy a probar esto que me adjuntas y en tenerlo, te digo el resultado.

Mil gracias por tu esfuerzo.

Un saludo!

[guilens2]

Hola de nuevo!

Funciona perfectamente!

He adaptado la consulta a mis necesidades, ya que necesito sacar el resultado de restar Número de Facturas - Número de Abonos realizados para cada cliente.

La relación de las tablas es de la siguiente manera:

Tabla Ventas: ventas_id, ventas_id_cliente
Tabla Clientes: cliente_id, cliente_compania
Tabla Facturas: facturas_id, facturas_ventas_id
Tabla Abonos: abonos_id, abonos_cliente_id

La tabla Facturas para llegar al cliente, necesita a la tabla ventas*.

Por lo tanto he hecho una adaptación de lo que tu me has dado, para quitar los NULL, hacer la resta de los totales y ordenarlos por nombre de compañia y queda así:

Código SQL:

SELECT C.cliente_id, C.cliente_compania, F.total_facturas - A.total_abonos
 FROM clientes C
 LEFT JOIN ( SELECT ventas_id_cliente, COUNT(*) total_facturas
             FROM facturas
 INNER JOIN ventas ON ventas_id=factura_ventas_id
             GROUP BY ventas_id_cliente) F ON C.cliente_id = F.ventas_id_cliente
 LEFT JOIN (SELECT abonos_id_cliente, COUNT(*) total_abonos
             FROM abonos
             GROUP BY abonos_id_cliente) A ON C.cliente_id = A.abonos_id_cliente 
-- -------------------
WHERE total_abonos IS NOT NULL AND total_facturas IS NOT NULL ORDER BY cliente_compania
-- -------------------
 

Resultado:
[B]cliente_id | cliente_compania | Total
38 COMPAÑIA UNO 10
48 COMPAÑIA DOS 6
53 COMPAÑIA TRES 4
54 COMPAÑIA CUATRO 2
56 COMPAÑIA CINCO 21
......

Un saludo y gracias de nuevo!