Consulta sobre cómo construir query en mysql

Question

Buen día. Necesito hacer una query en mysql y no estoy dando con la tecla.

Les comento:

Tengo una tabla A y B que tienen las siguientes columnas:

fecha, total

Suponiendo que la tabla A tiene los siguientes valores:

2020-07-02 00:00:00 3000.00
2020-07-03 00:00:00 9000.00
2020-07-04 00:00:00 1000.00
2020-07-06 00:00:00 400.00
2020-07-08 00:00:00 1400.00
2020-07-22 00:00:00 2400.00
2020-07-25 00:00:00 4000.00
2020-07-25 00:00:00 200.00
2020-07-28 00:00:00 100.00

Y la tabla B tiene los siguientes valores:

2020-07-02 00:00:00 1250
2020-07-02 00:00:00 1250
2020-07-02 00:00:00 790
2020-07-08 00:00:00 1300
2020-07-09 00:00:00 1500
2020-07-09 00:00:00 3000
2020-07-11 00:00:00 1500
2020-07-11 00:00:00 2500
2020-07-15 00:00:00 200
2020-07-15 00:00:00 624
2020-07-20 00:00:00 7670
2020-07-20 00:00:00 4090
2020-07-20 00:00:00 1000
2020-07-20 00:00:00 10000
2020-07-21 00:00:00 1693
2020-07-23 00:00:00 2500
2020-07-28 00:00:00 4880
2020-07-28 00:00:00 3620
2020-07-28 00:00:00 400.00

La idea sería hacer una query que me muestre por día los totales tanto de la tabla A y de la tabla B. Y si algún día, ya sea la tabla A o la tabla B no tiene registros, muestre 0.

Resumiendo, lo que yo espero con la query sería el siguiente resultado:

2020-07-02 3000 3290
2020-07-03 9000 0
2020-07-04 1000 0
2020-07-06 400 0
2020-07-08 1400 1300
2020-07-09 0 4500
2020-07-11 0 4000
2020-07-15 0 824
2020-07-20 0 22760
2020-07-21 0 1693
2020-07-22 2400 0
2020-07-23 0 2500
2020-07-25 4200 0
2020-07-28 100 8900

¿Es posible hacer una query que devuelva estos valores?

Esto es lo que tengo hecho hasta ahora, pero no me funciona como quiero:

select c.fecha_hora,
IFNULL(sum(c.total_compra),0),
v.fecha_hora,
IFNULL(sum(v.total_pago),0)
from 
compra c inner join venta v where c.fecha_hora = v.fecha_hora and monthname(v.fecha_hora)='July'
group by c.fecha_hora;

Desde ya muchas gracias.

y que as intentado hasta ahora podrias agregar tu progreso! necesitas ver tu query y lo que as intentando! de paso recomiendo ver [join sql](https://diego.com.es/principales-tipos-de-joins-en-sql) — Bryro, Sep 01 '20 at 13:51
esta es una comunidad de programadores, para ayudarte con tus dudas en tu código. Sin embargo si no agregas que estas intentando hacer, por mi parte lo tomo como una ofensa dado que aunque pueda responder tu respuesta no quiero darte haciendo tu tarea. — DavElsanto, Sep 01 '20 at 13:58
Una disculpa, ahí agregué en el texto lo que tengo hecho hasta ahora, pero no me funciona. — MG ML, Sep 01 '20 at 16:05

Ricardo Campos Villarroel · Answer 1 · 2020-09-01T18:58:33.810

Estimado.

La solución al requerimiento seria el siguiente:

Siendo A = tabla_A y B = tabla_B, se obtiene mediante un subquery el resultado de ambas tablas con el mismo formato de columnas para posteriormente unirlas. Como en la tabla A no hay resultados de B, se asigna una columna dummy con valor 0 (se aplica lo mismo para la tabla B). Se utiliza la sentencia UNION ALL, porque no queremos que se omitan valores duplicados al momento de unir, suponiendo que el contexto de las tablas es registrar movimientos por fechas, podría ocurrir la posibilidad que existan registros con fecha y total idénticos.

Teniendo el subquery como el resultado preliminar a consultar, podemos aplicar los criterios de suma y agrupación para obtener el resultado deseado.

SELECT
fecha_ab, sum(total_a), sum(total_b)
FROM
(
    SELECT
    tabla_A.fecha as fecha_ab, tabla_A.total as total_a, 0 as total_b
    FROM
    tabla_A

    UNION ALL

    SELECT
    tabla_B.fecha as fecha_ab, 0 as total_a, tabla_B.total as total_b
    FROM
    tabla_B
) 
as totales
GROUP BY fecha_ab

Al realizar la consulta tu resultado seria el siguiente:

Si bien siempre es ideal utilizar sentencias JOIN (y sus variantes), para este caso el contexto de la tablas tienen distinta naturaleza (compras y ventas por lo que veo en tu query), quizás lo ideal hubiese sido diseñar una tabla única que refleje los movimientos de entrada y salida.

Espero que haya sido de ayuda. :)

Cuando respondas trata de explicar tu código. Nota: Es una mala practica usar **UNION ALL**, solo cuando no se puede más es usado, sin embargo acá si veo que se pueda usar joins. — DavElsanto, Sep 01 '20 at 17:29
@DavElsanto, ¿Cuáles son los argumentos para decir que el uso de `union all` es una mala práctica? — jachguate, Sep 02 '20 at 02:19
has realizado alguna vez algún performance de consultas sql??? para un backend con millones de registros??? ese **union all** se va a demorar dado que tiene que hacer los 2 select unirlos y retornar la información. En cambio con un **join** cambia totalmente. Y por favor lee bien, digo claramente que se usa cuando no se puede más. https://www.essentialsql.com/what-is-the-difference-between-a-join-and-a-union/ — DavElsanto, Sep 02 '20 at 13:56

score 0 · Answer 2 · answered Sep 01 '20 at 19:06

Puedes hacer un full outer join de ambas tablas, relacionandolas por la fecha y sumar los valores.

Dado que cuando solo una de las tablas tenga una fecha, para la otra el campo fecha_hora será null, utiliza la función coalesce() para asegurarte que el campo fecha_hora del resultado siempre tenga un valor (que puede venir de cualquiera de las tablas).

Para asegurarte que haya un cero, en lugar de un null, si no hay datos de alguna de las columnas de resultado, utiliza también la función coalesce():

select   coalesce(c.fecha_hora, v.fecha_hora) fecha_hora
       , coalesce(sum(c.total_compra), 0)
       , coalesce(sum(v.total_pago), 0)
from compra c 
     full outer join venta v on c.fecha_hora = v.fecha_hora 
group by coalesce(c.fecha_hora, v.fecha_hora);

Consulta sobre cómo construir query en mysql

2 Answers2