Código SQL:
Ver originalCREATE TABLE #temp(
tipo VARCHAR(20),
dia INT,
cantidad INT
)
INSERT INTO #temp VALUES ('AEREO',1,5342)
INSERT INTO #temp VALUES ('AEREO',2,6504)
INSERT INTO #temp VALUES ('AEREO',3,5851)
INSERT INTO #temp VALUES ('AEREO',4,4171)
INSERT INTO #temp VALUES ('FLUVIAL',1,4479)
INSERT INTO #temp VALUES ('FLUVIAL',2,4555)
INSERT INTO #temp VALUES ('FLUVIAL',3,4375)
INSERT INTO #temp VALUES ('FLUVIAL',4,3269)
INSERT INTO #temp VALUES ('TERRESTRE',1,30838)
INSERT INTO #temp VALUES ('TERRESTRE',2,34528)
INSERT INTO #temp VALUES ('TERRESTRE',3,34434)
INSERT INTO #temp VALUES ('TERRESTRE',4,30431)
INSERT INTO #temp VALUES ('TOTAL',1,40659)
INSERT INTO #temp VALUES ('TOTAL',2,45587)
INSERT INTO #temp VALUES ('TOTAL',3,44660)
INSERT INTO #temp VALUES ('TOTAL',4,37871)
SELECT
t1.tipo, t1.dia, t1.cantidad / (t2.total * 1.0) AS promedio
FROM #temp AS t1
LEFT JOIN
(
SELECT SUM(cantidad) total,dia FROM #temp WHERE tipo<>'total' GROUP BY dia
) AS t2 ON (t1.dia=t2.dia)
WHERE t1.tipo<>'Total'
resultado:
tipo dia promedio
-------------------- ----------- ---------------------------------------
AEREO 1 0.13138542512112
AEREO 2 0.14267225305459
AEREO 3 0.13101209135691
AEREO 4 0.11013704417628
FLUVIAL 1 0.11016011215229
FLUVIAL 2 0.09991883651040
FLUVIAL 3 0.09796238244514
FLUVIAL 4 0.08631934725779
TERRESTRE 1 0.75845446272657
TERRESTRE 2 0.75740891043499
TERRESTRE 3 0.77102552619793
TERRESTRE 4 0.80354360856592