I need to obtain the average data for hours in a query but with a clause for column. I tried this:
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);