SQL Query to add fields from different tables

Question

SQL Query to add fields from different tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Someone who can 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 add all the fields with their respective values and then join the dates in a date block. So that way ...

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

I've tried two ways to do it with nested queries and subqueries. And in no way, I've got to do it. Can someone give 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

Subquerys

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 sql-server query

asked by Yoel Macia Delgado 08.08.2017 в 10:17

source

2 answers

0

Try an inner join, it worked for me, assuming that in the 3 tables you will have all the dates:

EDITED CONSULTATION

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 you.

answered by 08.08.2017 в 10:40

How to update fields by querie of databases of different servers? Fatal error: Uncaught Error: Call to a member function get () on null

score 1 · Accepted 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