sql – Join two queries

Question:

I have two queries that generate data for machine sales:

query 1:

SELECT
    Dia,
    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN 1 ELSE 0 END) AS 'Cantidad de Maquinas',
    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN Total ELSE 0 END) AS 'Total Maquinas'
FROM Reporte_maquinas
WHERE Dia <> 0
GROUP BY Dia
ORDER BY Dia

The information it generates:

Day Number of Machines Total Machines
6 16 29287
7 6 14235
8 63 257796
9 51 229244
12 324 1843430
13 163 738186
14 291 1897771
fifteen 177 490320
16 18 29272
17 12 13318
18 67 171970
19 746 2527861
twenty 267 1933228
twenty-one 3130 12975689

query 2:

SELECT
    Dia_factura,
    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN 1 ELSE 0 END) AS '# Cantidad de Maquinas',
    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN Total ELSE 0 END) AS '# Total Maquinas'
FROM Reporte_maquinas
WHERE Dia_factura <> 0
GROUP BY Dia_factura
ORDER BY Dia_factura

Information generated:

Invoice_day # Of Machines # Total Machines
9 12 56728
12 38 187674
13 151 939645
14 82 239753
fifteen 155 552393
19 83 176491
twenty 77 318991
twenty-one 1860 10648261

The point is that I have to unify the queries to generate only one result, similar to this:

Day Number of Machines Total Machines # Of Machines # Total Machines
6 16 29287 0 0
7 6 14235 0 0
8 63 257796 0 0
9 51 229244 12 56728
12 324 1843430 38 187674
13 163 738186 151 939645
14 291 1897771 82 239753
fifteen 177 490320 155 552393
16 18 29272 0 0
17 12 13318 0 0
18 67 171970 0 0
19 746 2527861 83 176491
twenty 267 1933228 77 318991
twenty-one 3130 12975689 1860 10648261

Try using this query, but it doesn't generate the corresponding values ​​and I'm out of ideas:

SELECT
    Dia,
    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN 1 ELSE 0 END) AS 'Cantidad de Maquinas',
    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN Total ELSE 0 END) AS 'Total Maquinas',
    SUM(CASE WHEN EstadoLectura = 'LEIDO' AND Dia_factura <> 0 AND Dia_factura IS NOT NULL THEN 1 ELSE 0 END) AS '# Cantidad de Maquinas',
    SUM(CASE WHEN EstadoLectura = 'LEIDO' AND Dia_factura <> 0 AND Dia_factura IS NOT NULL THEN Total ELSE 0 END) AS '# Total Maquinas'
FROM Reporte_maquinas
WHERE Dia <> 0
GROUP BY Dia
ORDER BY Dia

I hope you can help me.

Answer:

You cannot join the two queries, because as I understand it, the same row could have one dia and a different one in Dia_factura , which would require expanding that row by two. What you can do is join both queries and then group by day:

SELECT  T.Dia,
        SUM(T.cant_maquinas_d)     as 'Cantidad de Maquinas'
        SUM(T.total_maquinas_d)    as 'Total Maquinas'
        SUM(T.cant_maquinas_f)     as '# Cantidad de Maquinas'
        SUM(T.total_maquinas_f)    as '# Total Maquinas'
        FROM (
            SELECT  Dia_factura as Dia,
                    0,                                                              AS cant_maquinas_d,
                    0,                                                              AS total_maquinas_d,
                    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN 1 ELSE 0 END)        AS cant_maquinas_f,
                    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN Total ELSE 0 END)    AS total_maquinas_f
                    FROM Reporte_maquinas
                    WHERE Dia_factura <> 0
                    GROUP BY Dia_factura

            UNION ALL

            SELECT  Dia,
                    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN 1 ELSE 0 END)         AS cant_maquinas_d,
                    SUM(CASE WHEN EstadoLectura = 'LEIDO' THEN Total ELSE 0 END)     AS total_maquinas_d,
                    0                                                                AS cant_maquinas_f,
                    0                                                                AS total_maquinas_f
                    FROM Reporte_maquinas
                    WHERE Dia <> 0
                    GROUP BY Dia
        ) AS T
        GROUP BY T.Dia
Scroll to Top