MySQL - Unir 3 tablas y y sumar los valores de las columnas

Unir 3 tablas y y sumar los valores de las columnas

Publicado por Martha (80 intervenciones) el 12/12/2021 13:33:56

Hola, no me ha quedado muy claro qué es lo que quieres sumar, pero lo he hecho de dos formas diferentes.

Para empezar me he creado una base de datos con tres tablas, dónde los campos no se llaman igual, pero son del mismo tipo.

Está en MariaDB 10, pero seguro que te funciona en MySQL 8.

Seis campos integer y dos campos varchar(20), uno con el código y otro con un dato que al parecer no necesitas.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

-- phpMyAdmin SQL Dump

-- version 5.1.1

-- https://www.phpmyadmin.net/

--

-- Servidor: 127.0.0.1

-- Tiempo de generación: 12-12-2021 a las 13:24:39

-- Versión del servidor: 10.4.21-MariaDB

-- Versión de PHP: 8.0.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

START TRANSACTION;

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

--

-- Base de datos: `test`

--

-- --------------------------------------------------------

--

-- Estructura de tabla para la tabla `t1`

--

CREATE TABLE `t1` (

  `t1_id` int(11) NOT NULL,

  `uno_1` int(11) DEFAULT NULL,

  `dos_1` int(11) DEFAULT NULL,

  `tres_1` int(11) DEFAULT NULL,

  `cuatro_1` int(11) DEFAULT NULL,

  `cinco_1` int(11) DEFAULT NULL,

  `seis_1` int(11) DEFAULT NULL,

  `siete_1` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,

  `ocho_1` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

--

-- Volcado de datos para la tabla `t1`

--

INSERT INTO `t1` (`t1_id`, `uno_1`, `dos_1`, `tres_1`, `cuatro_1`, `cinco_1`, `seis_1`, `siete_1`, `ocho_1`) VALUES

(1, 12, 11, 10, 18, 12, 18, 'Codigo9', 'Hotel'),

(2, 11, 15, 16, 11, 19, 20, 'Codigo1', 'Avión'),

(3, 17, 15, 11, 18, 14, 12, 'Codigo9', 'Desayunos'),

(4, 14, 12, 13, 12, 12, 14, 'Codigo8', 'Comidas'),

(5, 14, 11, 11, 18, 14, 11, 'Codigo3', 'Taxi'),

(6, 16, 19, 13, 19, 16, 19, 'Codigo9', 'Teléfono'),

(7, 16, 15, 13, 17, 16, 15, 'Codigo2', 'Taxi'),

(8, 12, 18, 17, 13, 20, 16, 'Codigo5', 'Tren'),

(9, 19, 12, 11, 19, 16, 16, 'Codigo3', 'Desayunos'),

(10, 15, 17, 14, 15, 17, 18, 'Codigo9', 'Desayunos'),

(11, 15, 14, 18, 11, 11, 10, 'Codigo10', 'Gastos Varios'),

(12, 12, 18, 17, 20, 12, 13, 'Codigo7', 'Tren'),

(13, 20, 11, 10, 20, 14, 20, 'Codigo2', 'Tren'),

(14, 16, 13, 17, 10, 13, 11, 'Codigo3', 'Tren'),

(15, 18, 14, 16, 15, 18, 15, 'Codigo10', 'Gastos Varios'),

(16, 11, 13, 11, 11, 12, 19, 'Codigo3', 'Hotel'),

(17, 19, 19, 20, 17, 12, 16, 'Codigo5', 'Hotel'),

(18, 19, 12, 20, 17, 14, 12, 'Codigo5', 'Teléfono'),

(19, 15, 10, 17, 11, 13, 12, 'Codigo9', 'Taxi'),

(20, 15, 12, 10, 11, 10, 19, 'Codigo8', 'Tren'),

(21, 19, 18, 15, 14, 12, 19, 'Codigo5', 'Tren'),

(22, 18, 13, 17, 12, 19, 16, 'Codigo7', 'Avión'),

(23, 16, 14, 18, 18, 14, 10, 'Codigo3', 'Cenas'),

(24, 17, 14, 20, 13, 12, 15, 'Codigo2', 'Desayunos');

-- --------------------------------------------------------

--

-- Estructura de tabla para la tabla `t2`

--

CREATE TABLE `t2` (

  `t2_id` int(11) NOT NULL,

  `uno_2` int(11) DEFAULT NULL,

  `dos_2` int(11) DEFAULT NULL,

  `tres_2` int(11) DEFAULT NULL,

  `cuatro_2` int(11) DEFAULT NULL,

  `cinco_2` int(11) DEFAULT NULL,

  `seis_2` int(11) DEFAULT NULL,

  `siete_2` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,

  `ocho_2` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

--

-- Volcado de datos para la tabla `t2`

--

INSERT INTO `t2` (`t2_id`, `uno_2`, `dos_2`, `tres_2`, `cuatro_2`, `cinco_2`, `seis_2`, `siete_2`, `ocho_2`) VALUES

(1, 18, 17, 15, 11, 15, 11, 'Codigo6', 'Cenas'),

(2, 11, 16, 19, 16, 16, 13, 'Codigo10', 'Avión'),

(3, 11, 14, 20, 16, 12, 18, 'Codigo2', 'Teléfono'),

(4, 11, 14, 18, 20, 13, 20, 'Codigo8', 'Comidas'),

(5, 12, 20, 17, 11, 13, 16, 'Codigo5', 'Gastos Varios'),

(6, 16, 18, 17, 15, 17, 16, 'Codigo1', 'Gastos Varios'),

(7, 11, 19, 10, 18, 12, 15, 'Codigo1', 'Kilómetros'),

(8, 11, 13, 20, 12, 19, 17, 'Codigo9', 'Taxi'),

(9, 11, 17, 18, 14, 18, 12, 'Codigo2', 'Gastos Varios'),

(10, 20, 16, 19, 12, 15, 13, 'Codigo1', 'Teléfono'),

(11, 17, 18, 15, 15, 15, 11, 'Codigo3', 'Comidas'),

(12, 17, 12, 15, 14, 18, 17, 'Codigo1', 'Teléfono'),

(13, 14, 12, 19, 14, 10, 11, 'Codigo2', 'Comidas'),

(14, 19, 20, 18, 11, 14, 10, 'Codigo6', 'Desayunos'),

(15, 13, 11, 18, 19, 17, 14, 'Codigo10', 'Avión'),

(16, 14, 18, 20, 15, 12, 11, 'Codigo8', 'Hotel'),

(17, 10, 15, 20, 14, 19, 20, 'Codigo1', 'Taxi'),

(18, 15, 12, 19, 18, 14, 20, 'Codigo3', 'Cenas'),

(19, 18, 12, 19, 15, 20, 14, 'Codigo1', 'Tren'),

(20, 20, 18, 15, 14, 18, 14, 'Codigo1', 'Gastos Varios'),

(21, 15, 12, 14, 15, 20, 16, 'Codigo10', 'Tren'),

(22, 18, 15, 12, 12, 19, 10, 'Codigo7', 'Desayunos'),

(23, 15, 18, 17, 18, 16, 12, 'Codigo7', 'Hotel'),

(24, 20, 13, 16, 16, 13, 20, 'Codigo7', 'Comidas');

-- --------------------------------------------------------

--

-- Estructura de tabla para la tabla `t3`

--

CREATE TABLE `t3` (

  `t3_id` int(11) NOT NULL,

  `uno_3` int(11) DEFAULT NULL,

  `dos_3` int(11) DEFAULT NULL,

  `tres_3` int(11) DEFAULT NULL,

  `cuatro_3` int(11) DEFAULT NULL,

  `cinco_3` int(11) DEFAULT NULL,

  `seis_3` int(11) DEFAULT NULL,

  `siete_3` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL,

  `ocho_3` varchar(20) COLLATE utf8mb4_spanish_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

--

-- Volcado de datos para la tabla `t3`

--

INSERT INTO `t3` (`t3_id`, `uno_3`, `dos_3`, `tres_3`, `cuatro_3`, `cinco_3`, `seis_3`, `siete_3`, `ocho_3`) VALUES

(1, 17, 12, 13, 10, 19, 14, 'Codigo2', 'Taxi'),

(2, 19, 17, 16, 17, 15, 17, 'Codigo10', 'Tren'),

(3, 12, 19, 18, 16, 16, 12, 'Codigo5', 'Avión'),

(4, 17, 15, 15, 18, 20, 11, 'Codigo5', 'Gastos Varios'),

(5, 16, 16, 16, 14, 10, 12, 'Codigo8', 'Comidas'),

(6, 11, 20, 12, 19, 12, 13, 'Codigo8', 'Cenas'),

(7, 14, 10, 15, 13, 15, 12, 'Codigo8', 'Tren'),

(8, 19, 15, 13, 14, 20, 16, 'Codigo4', 'Taxi'),

(9, 20, 15, 14, 15, 17, 10, 'Codigo1', 'Cenas'),

(10, 14, 12, 19, 17, 19, 10, 'Codigo1', 'Teléfono'),

(11, 17, 16, 12, 19, 17, 14, 'Codigo3', 'Desayunos'),

(12, 20, 16, 15, 15, 13, 18, 'Codigo7', 'Cenas'),

(13, 13, 15, 12, 19, 12, 16, 'Codigo7', 'Teléfono'),

(14, 11, 13, 15, 10, 13, 20, 'Codigo1', 'Desayunos'),

(15, 15, 13, 17, 20, 12, 14, 'Codigo10', 'Avión'),

(16, 20, 18, 19, 17, 19, 20, 'Codigo9', 'Tren'),

(17, 14, 19, 13, 17, 20, 16, 'Codigo5', 'Gastos Varios'),

(18, 18, 15, 16, 20, 14, 16, 'Codigo3', 'Cenas'),

(19, 12, 15, 12, 14, 12, 19, 'Codigo3', 'Tren'),

(20, 12, 15, 20, 14, 11, 20, 'Codigo4', 'Taxi'),

(21, 11, 17, 18, 12, 16, 17, 'Codigo6', 'Cenas'),

(22, 14, 11, 20, 10, 13, 14, 'Codigo8', 'Cenas'),

(23, 16, 15, 14, 20, 16, 13, 'Codigo7', 'Avión'),

(24, 19, 20, 12, 14, 15, 15, 'Codigo2', 'Cenas');

--

-- Índices para tablas volcadas

--

--

-- Indices de la tabla `t1`

--

ALTER TABLE `t1`

  ADD PRIMARY KEY (`t1_id`);

--

-- Indices de la tabla `t2`

--

ALTER TABLE `t2`

  ADD PRIMARY KEY (`t2_id`);

--

-- Indices de la tabla `t3`

--

ALTER TABLE `t3`

  ADD PRIMARY KEY (`t3_id`);

--

-- AUTO_INCREMENT de las tablas volcadas

--

--

-- AUTO_INCREMENT de la tabla `t1`

--

ALTER TABLE `t1`

  MODIFY `t1_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--

-- AUTO_INCREMENT de la tabla `t2`

--

ALTER TABLE `t2`

  MODIFY `t2_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--

-- AUTO_INCREMENT de la tabla `t3`

--

ALTER TABLE `t3`

  MODIFY `t3_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Una vez tenemos esas tres tablas, las unimos mediante una consulta de UNION, a la cual llamamos T y a esa tabla le hacemos consultas.

Por un lado he sumado cada campo, y por otro los he sumado todos. Seguro que podrás adaptar el resultado a tu base de datos.

Las consultas son estas:

SELECT sum(t.uno),sum(t.dos),sum(t.tres),sum(t.cuatro),sum(t.cinco),sum(t.seis), t.siete

FROM

(SELECT

uno_1 as uno, dos_1 as dos, tres_1 as tres, cuatro_1 as cuatro, cinco_1 as cinco, seis_1 as seis, siete_1 as siete

FROM t1 UNION SELECT

uno_2 as uno, dos_2 as dos, tres_2 as tres, cuatro_2 as cuatro, cinco_2 as cinco, seis_2 as seis, siete_2 as siete

FROM t2	UNION SELECT

uno_3 as uno, dos_3 as dos, tres_3 as tres, cuatro_3 as cuatro, cinco_3 as cinco, seis_3 as seis, siete_3 as siete

FROM t3) as t

GROUP BY t.siete;

SELECT sum(t.uno)+sum(t.dos)+sum(t.tres)+sum(t.cuatro)+sum(t.cinco)+sum(t.seis) as Sumatorio, t.siete

FROM

(SELECT

uno_1 as uno, dos_1 as dos, tres_1 as tres, cuatro_1 as cuatro, cinco_1 as cinco, seis_1 as seis, siete_1 as siete

FROM t1 UNION SELECT

uno_2 as uno, dos_2 as dos, tres_2 as tres, cuatro_2 as cuatro, cinco_2 as cinco, seis_2 as seis, siete_2 as siete

FROM t2	UNION SELECT

uno_3 as uno, dos_3 as dos, tres_3 as tres, cuatro_3 as cuatro, cinco_3 as cinco, seis_3 as seis, siete_3 as siete

FROM t3) as t

GROUP BY t.siete;

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar