SELECT MYSQL query unrelated tables

Question

SELECT MYSQL query unrelated tables

Navigation

#1 by (2 votes)

1

I have the following table called purchases

And a sales call

of which I need to obtain a total sum grouped by month. Just to give an example of what I'm trying to do, I have this query.

SELECT * FROM (SELECT DISTINCT MONTH(creado) as meses,
IFNULL(SUM(total),0) as total_mes  FROM ventas WHERE YEAR(creado) =
'2018' AND idsucursal='1' AND estado='Enviado' GROUP BY meses ORDER BY
meses DESC) as ventas,(SELECT DISTINCT MONTH(fecha_compra) as mesess,
IFNULL(SUM(total),0) as total_mes_compras  FROM compras WHERE
YEAR(fecha_compra) = '2018' AND idsucursal='1' AND estado='Aceptado'

GROUP BY mesess ORDER BY mesess DESC) as purchases GROUP BY months, mesess

and the result I get is

and the result I want to obtain would be like that.

Please if you can help me, I'm already breaking my head. Thanks

mysql select

asked by JohnL 19.09.2018 в 00:07

source

1 answer

Avoid loading certain fields of an eloquent query How to make an arrangement of objects within another class? c ++

score 2 · Accepted Answer

You can make the two subqueries separately and then join them, that way you'll get what you expect.

Next I present an example to you, which I try with tables of a scheme that I have in my computer, but I change them to the tables and fields of your example, reason why something can fail, but this will serve as a guide to get the expected result.

Example:

SELECT s.mes, sum(s.total_mes_venta), sum(s.total_mes_compra) FROM 
(
    SELECT MONTH(creado) mes, sum(total) AS total_mes_venta, 0 AS total_mes_compra FROM ventas
    WHERE YEAR(creado) = '2018' AND idsucursal='1' AND estado='Enviado'
    GROUP BY MONTH(creado)
    UNION ALL
    SELECT MONTH(fecha_compra) mes, 0 as total_mes_venta, SUM(total) AS total_mes_compra FROM compras
    WHERE YEAR(fecha_compra) = '2018' AND idsucursal='1' AND estado='Aceptado'
    GROUP BY MONTH(fecha_compra)
) s
GROUP BY s.mes