When calculating averages in SQL Server using integer columns, standard queries can silently distort your metrics. For example, let’s take the following dataset on Employee data for various engineering roles, where the salary and rating columns are INT type. SQL Server evaluates the expression using integer division and silently drops the decimal portion.
-- table structureCREATE TABLE EMPLOYEE (
empId INT PRIMARY KEY,name VARCHAR(25),
dept VARCHAR(25),
salary INT,
rating INT CONSTRAINT CK_EMPLOYEE_rating CHECK (rating BETWEEN 1 AND 5));
-- sample records (first 4 of 27 rows; full script on GitHub)INSERT INTO EMPLOYEE (empId, name, dept, salary, rating) VALUES
-- Software Development Engineer (SDE)
(1, 'Zaara', 'SDE 1', 1800000, 3),
(2, 'Aarav', 'SDE 2', 3800000, 4),
(3, 'Priya', 'SDE 3', 6500000, 5),
(4, 'Rohan', 'Lead SDE', 8500000, 5),
...
So when we take the average of salary and rating columns, we see the following result displayed:
SELECT AVG(salary) AS avg_salary, AVG(rating) AS avg_rating FROM EMPLOYEE;
Whereas in reality, the sum of all salaries divided by the number of employees generates a decimal number, the final result gets truncated, meaning the decimal portion gets dropped completely. Similarly, when we take the average of the rating column, its decimal portion gets dropped.
Let’s fix this by altering the data types to DECIMAL (making sure to handle our constraints properly) and see the updated results:
ALTER TABLE EMPLOYEE ALTER COLUMN salary DECIMAL(18,2);-- drop the CHECK constraint so the column type can changeALTER TABLE EMPLOYEE DROP CONSTRAINT CK_EMPLOYEE_rating; ALTER TABLE EMPLOYEE ALTER COLUMN rating DECIMAL(3,2);ALTER TABLE EMPLOYEE ADD CONSTRAINT CK_EMPLOYEE_rating CHECK (rating BETWEEN 1 AND 5);
Then, recompute the average, the same as before:
SELECT AVG(salary) AS avg_salary, AVG(rating) AS avg_rating FROM EMPLOYEE;
This time the output shows the accurate decimal portions as well:
What we see is that using DECIMAL gives us the actual average with the decimal part intact. While the difference between the two for avg_salary doesn’t seem like much on large numbers, there is a massive distortion in the average rating results. The actual average rating should have been 3.96, but it got truncated down to just 3.
In real-world cases, where we deal with large volumes of records and aggregate metrics like performance scores or ratings, finding the exact average requires us to cast or store our numerical metrics with fractional precision (like DECIMAL or FLOAT) even if the raw inputs are integers.
Have you ever noticed the unexpected difference between the results of an AVG() function for an INT versus a DECIMAL/FLOAT type column in MS SQL Server?
This was Day 4 of my 30 Days of Data Science writing challenge. While the prompt felt simple, it took some amount of research to understand how how SQL Server computes averages.
You can see the complete SQL query on my GitHub: https://github.com/Rupali-Gauravaram/dev-prompt-generator/blob/main/posts/post_04-employee_data.sql
Top comments (0)