I need to obtain the average data for hours in a query but with a clause for column. I tried this:
x
SELECT DATEPART(hour, Fecha) as Hora, CONVERT(INT, AVG(particulas))as PARTICULAS
from filtro1
where flag1 ='0' and fecha >= '16-03-2020 17:20:00.370' and fecha <= '17-03-2020 06:20:00.370' GROUP
BY DATEPART(hour, Fecha)
Union
SELECT DATEPART(hour, Fecha) as Hora ,AVG(presion) AS PRESION
from filtro1
where flag2 ='0' and fecha >= '16-03-2020 17:20:00.370' and fecha <= '17-03-2020 06:20:00.370'
GROUP BY DATEPART(hour, Fecha)
ORDER BY Hora
But the result is this:
I need the result in differents columns but with the same hour row.
Advertisement
Answer
Do conditional aggregation :
SELECT DATEPART(hour, Fecha) AS Hora,
AVG(CASE WHEN FLAG1 = 0 THEN particulas END) AS PARTICULAS,
AVG(CASE WHEN FLAG2 = 0 THEN presion END) AS PRESION
FROM filtro1
WHERE fecha >= '2020-03-16 17:20:00.370' AND
fecha <= '2020-03-17 06:20:00.370' AND
0 IN (FLAG1, FLAG2)
GROUP BY DATEPART(hour, Fecha);