sqlaveragestdev

sql AVARAGE OF STDEV

I want the result of the avarage of the STDEV of a period of days. What is wrong on my code?

select avg(X_MESURE) AS AVG_MESURE from (SELECT cast(DATE_PEREMPTION as date) as DATE_PEREMPTION, ROUND(STDEV(MESURE),3) AS X_MESURE FROM [MECMAS].[dbo].[T_Batch] B Join [MECMAS].[DBO].[TR_BATCH_MP] MP ON ([MP].ID_BATCH =B.ID ) Join [mecmas].[dbo].[T_DONNEES] d on (d.ID=MP.ID_DONNEES) WHERE B.DATE_PEREMPTION >= '2022-01-01 00:00:00.00' AND NAME='AGUA' group by cast(DATE_PEREMPTION as date)) ORDER BY X_MESURE

enter image description here

Solution

  • Either remove order by clause or use it inside parenthesis as below

    select avg(X_MESURE) AS AVG_MESURE
from (SELECT cast(DATE_PEREMPTION as date) as DATE_PEREMPTION, ROUND(STDEV(MESURE),3) AS X_MESURE 
FROM [MECMAS].[dbo].[T_Batch] B 
Join [MECMAS].[DBO].[TR_BATCH_MP] MP 
ON ([MP].ID_BATCH =B.ID ) 
Join [mecmas].[dbo].[T_DONNEES] d 
on (d.ID=MP.ID_DONNEES) 
WHERE B.DATE_PEREMPTION >= '2022-01-01 00:00:00.00' AND NAME='AGUA' 
group by cast(DATE_PEREMPTION as date 
ORDER BY X_MESURE))