Home / Questions / 93538
Accepted
Yoel Macia Delgado
Asked: 2020-08-09 00:17:51 +0800 CST 2020-08-09 00:17:51 +0800 CST

SQL Query to add fields from different tables

  • 772

Can someone help me with this query? I have three tables with different names.

Tabla Ejemplo1
fecha         campo1
2017-01-01    0.00
2017-01-01    7.00
2017-01-01    0.00
2017-01-01    1.00
2017-01-01    0.00
2017-01-02    1.00
2017-01-02    43.00
2017-01-02    1.00
2017-01-02    0.00
2017-01-02    0.00

Tabla Ejemplo2
fecha         campo2
2017-01-01    0.00
2017-01-01    5.00
2017-01-01    1.00
2017-01-01    3.00
2017-01-01    0.00
2017-01-02    1.00
2017-01-02    3.00
2017-01-02    2.00
2017-01-02    2.00
2017-01-02    0.00

Tabla Ejemplo3
fecha         campo3
2017-01-01    0.00
2017-01-01    5.00
2017-01-01    1.00
2017-01-01    3.00
2017-01-01    0.00
2017-01-02    1.00
2017-01-02    1.00
2017-01-02    0.00
2017-01-02    0.00
2017-01-02    0.00

I want to sum all the fields with their respective values ​​and then join the dates in a date block. Like so...

Tabla Final
fecha         campoTotal
2017-01-01    26.00
2017-01-02    55.00

I have tried two ways to do it with nested queries and with subqueries. And no way, I have come to do it. Someone might take me a cable?

queries

SELECT [fecha],
       sum([campo1]) AS "campo1"
FROM Ejemplo1
WHERE fecha = '2017-01-01'
GROUP BY fecha

SELECT [fecha],
       sum([campo2]) AS "campo2"
FROM Ejemplo2
WHERE fecha = '2017-01-01'
GROUP BY fecha

SELECT [fecha],
       sum([campo3]) AS "campo3"
FROM Ejemplo3
WHERE fecha = '2017-01-01'
GROUP BY fecha

Subqueries

select fecha,sum(campo1) total
from
(
    select fecha,campo1
    from ejemplo1
    union all
    select fecha,campo2
    from ejemplo2
    union all
    select fecha,campo3
    from ejemplo3
) t
group by fecha
sql-server

2 Answers

  1. Best Answer

    I have solved it as you said using an INNER JOIN of the tables.

    SELECT conversions_1.fecha,
       ([columna 1] + [columna 2] + [columna 3] as conversiones_totales
       FROM
(SELECT [fecha],
       sum([campo1]) AS "columna 1"
FROM [dbo].[Ejemplo1]
GROUP BY fecha) as conversions_1

inner join

(SELECT [fecha],
       sum([campo2]) AS "columna 2"
FROM [dbo].[Ejemplo2]
GROUP BY fecha) as conversions_2

on conversions_1.fecha = conversions_2.fecha

inner join

(SELECT [fecha],
       sum([campo3]) AS "columna 3"
FROM [dbo].[Ejemplo3]
GROUP BY fecha) as conversions_3

on conversions_1.fecha = conversions_3.fecha

ORDER BY conversions_1.fecha
    • 2

  2. Try an inner join, it has worked for me, assuming that in the 3 tables you are going to have all the dates:

    EDITED QUERY

    SELECT e1.fecha, (e1.campo1 + e2.campo2 + e3.campo3) from ejemplo1 e1 inner join ejemplo2 e2 on e1.fecha = e2.fecha inner join ejemplo3 e3 on e1.fecha = e3.fecha group by fecha;

    I hope it helps.

    • 0