Skip to content
Advertisement

Average data for hours in a query

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:

enter image description here

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);
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement