CODEWITHSUNDEEP
sqlsql-server
Marcos Alejandro Pérez
Marcos Alejandro Pérez

Reputation: 181

SQL Server AVG returning 0 for every row

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

Upvotes: 1

Views: 778

Answers (1)

FrankPl
FrankPl

Reputation: 929

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.

Upvotes: 2

Related Questions