SQL Server AVG returning 0 for every row

Question

I'm trying to make a somewhat complex query on SQL Server. Had to do a select from a select in order to group by aliases. The problem is that the sum and the count return correct values, but the average returns always zero. if I calculate it as sum(quer.score)/count(quer.score) it also returns all zeros.

What am I doing wrong?

Thanks in advance!

SELECT quer.month, quer.item_type, SUM(quer.score) AS sum_values,COUNT(quer.score) as count_values, avg(quer.score) as final_value FROM (
SELECT
CASE
WHEN MONTH(inte.date) = 1 THEN 'January'
WHEN MONTH(inte.date) = 2 THEN 'February'
WHEN MONTH(inte.date) = 3 THEN 'March'
WHEN MONTH(inte.date) = 4 THEN 'April'
WHEN MONTH(inte.date) = 5 THEN 'May'
WHEN MONTH(inte.date) = 6 THEN 'June'
WHEN MONTH(inte.date) = 7 THEN 'July'
WHEN MONTH(inte.date) = 8 THEN 'Agosto'
WHEN MONTH(inte.date) = 9 THEN 'September'
WHEN MONTH(inte.date) = 10 THEN 'October'
WHEN MONTH(inte.date) = 11 THEN 'November'
WHEN MONTH(inte.date) = 12 THEN 'December'
END AS month,

CASE
    WHEN  inte.item_id in (SELECT distinct item_id from Items where item_type = 'electronic') THEN 'electronic'
    ELSE 'not electronic'
END AS item_type,

CASE WHEN scores.score <7 THEN -1
WHEN scores.score >8 THEN 1
ELSE 0
END AS score
FROM internal_items inte inner join scores_data scores on inte.item_id = scores.item_id
WHERE inte.internal_type = 'dba'
) quer

GROUP BY quer.month, quer.item_type

Result:

month     item_type           sum  count  avg
January   electronic           0     3     0
January   not electronic      -2     7     0
February  electronic          -4     6     0
February  not electronic      -6     8     0
March     electronic          -4     5     0
March     not electronic      -3     6     0

If `score` is an `int` then an `int` will be returned. I *assume* the average is less than `1` and greater than `-1` and hence represented as `0`. You'll need to cast the value to an appropriate decimal *before* you average it. — Thom A, Mar 04 '21 at 13:32
Also, your `CASE` expression looks odd; it will return zero for either 8 or NULL. — Dan Guzman, Mar 04 '21 at 13:35
Does this answer your question? [AVG in Sql - Float number problem](https://stackoverflow.com/questions/1102406/avg-in-sql-float-number-problem) — Charlieface, Mar 04 '21 at 14:27

FrankPl · Accepted Answer · 2021-03-04T14:34:36.767

2

You should use decimals or floats, not integers as data type, e. g. use

CASE WHEN scores.score <7 THEN -1.0
WHEN scores.score >8 THEN 1.0
ELSE 0.0
END AS score

(note the .0 added).

SQL Server uses the same data type as the base column for the aggregate result, and thus, the average would be calculated as int when the input values are ints.

edited Mar 04 '21 at 14:34

answered Mar 04 '21 at 13:46

FrankPl

919
3
12

SQL Server AVG returning 0 for every row

1 Answers1