Average function on a group by shows wrong number for TSQL query

by

I have used the below query:

select format(CreatedOn, 'yyyy', 'fa') as Date, 
       sum((elg_Behaviour-861239999)) as Sum,
       count(*) as Count, 
       AVG((elg_Behaviour-861239999)) as Average 
  from elg_surveyBase  
 group by format(CreatedOn, 'yyyy', 'fa')

The requirement is to show the average grouped by column 1, but avg is always 1.

Date Sum Count Average
1400 24 20 1
1401 69 51 1
1402 26 19 1

It should be 24/20= 1.2 or 69/51=1.35

  • 1

    We will need some example data which clearly shows the issue you’re having. A good way to provide it is: DECLARE @Table TABLE (Col1 INT, Col2 INT, ...); INSERT INTO @Table (Co1, Col2,...) VALUES (1, 1,...), (2, 2,...)...;

    – 

  • 2

    I’d guess that elg_Behaviour is an int? The AVG of an int will be an int; if you don’t want an int, then CONVERT/CAST the value before you average it. Or, as you are subtracting 861239999 from the value, make that value a numeric: 861239999.

    – 




Leave a Comment