fusion sql queries

Question

fusion sql queries

Navigation

#1 by (3 votes)

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

mysql

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

source

1 answer

Pass variables and perform actions between classes in C # Concatenate variables in C ++

score 3 · Answer 1

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