CODEWITHSUNDEEP
sqlsql-server
mourazo
mourazo

Reputation: 127

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.

Upvotes: 0

Views: 64

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want accurate values for the the averages, then you need NULL values for the non-matches. ININaddition, you should use proper date formats:

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)
ORDER BY MIN(Fecha);

Upvotes: 0

TomTom
TomTom

Reputation: 62093

  • Make a query that returns both data cells in SEPARATE ROWS.

  • Wrap them in a PIVOT statement that creates a projection that moves them into separate rows.

Pivot is a little gem but likely hardly anyone knows it. Requires decently current version of SQL Server.

Syntax in the manual. I am on the go now and have no time to look up the details.

Upvotes: 0

Related Questions