# fusion sql queries

4
SELECT Date(f.Fecha),SUM(f.Total) as fac FROM factura f WHERE
f.Tipo='Factura' AND f.Fecha BETWEEN '2018-09-20' AND '2018-09-25' GROUP BY
Date(f.Fecha);
SELECT Date(s.Fecha),SUM(s.Total) as sal FROM sales s WHERE s.Fecha BETWEEN
'2018-09-20' AND '2018-09-25' GROUP BY Date(s.Fecha);
SELECT Date(a.Fecha),SUM(a.Abono) as abo FROM abonos a WHERE a.Fecha
BETWEEN '2018-09-20' AND '2018-09-25' GROUP BY Date(a.Fecha);

HOW DO I OBTAIN THESE 3 RESULTS IN A SINGLE QUERY?

fecha    tabla1 tabla2  tabla3  total
9/13/2018   xxx xxx xx  xx
9/14/2018   xxx xxx xx  xx
9/15/2018   xxx xxx xx  xx
9/16/2018   xxx xxx xx  xx
9/17/2018   xxx xxx xx  xx
9/18/2018   xxx xxx xx  xx
9/19/2018   xxx xxx xx  xx
9/20/2018   xxx xxx xx  xx
9/21/2018   xxx xxx xx  xx
9/22/2018   xxx xxx xx  xx
9/23/2018   xxx xxx xx  xx

Something like that I hope to receive

asked by Richard Víquez Pérez 27.09.2018 в 01:23
source

3

You could basically solve it with a UNION ALL and a subquery. With the UNION ALL we join the three queries in a single recordset, it should be UNION ALL , a UNION simple would eliminate possible duplicate rows something we do not want. These results will be a subquery that will be grouped by Date. We also add a conditional sum to also have the subtotals that make up the grand total.

SELECT Fecha,
SUM(CASE WHEN T.Consulta = 1 THEN T.Total ELSE 0 END) AS Factura,
SUM(CASE WHEN T.Consulta = 2 THEN T.Total ELSE 0 END) AS Sales,
SUM(CASE WHEN T.Consulta = 3 THEN T.Total ELSE 0 END) AS Abonos,
SUM(T.Total)                                          AS Total
FROM (
SELECT 1 AS Consulta, Date(f.Fecha) as Fecha, SUM(f.Total) as Total
FROM factura f
WHERE f.Tipo='Factura' AND f.Fecha BETWEEN '2018-09-20' AND '2018-09-25'
GROUP BY Date(f.Fecha)

UNION ALL

SELECT 2, Date(s.Fecha), SUM(s.Total)
FROM sales s
WHERE s.Fecha BETWEEN '2018-09-20' AND '2018-09-25'
GROUP BY Date(s.Fecha)

UNION ALL

SELECT 3, Date(a.Fecha), SUM(a.Abono)
FROM abonos a
WHERE a.Fecha BETWEEN '2018-09-20' AND '2018-09-25'
GROUP BY Date(a.Fecha)
) T
GROUP BY T.Fecha
ORDER BY T.Fecha