Limit result of query to 2 decimal places

• Question

• Hello, how could i limit my results of the column PercentDiff to being shown with only 2 decimals

My code:

SELECT employees.last_name, employees.department_id, employees.salary, emp_salary_stat.avg, 100*(employees.salary-emp_salary_stat.avg)/employees.salary AS PercentDiff
FROM employees
INNER JOIN emp_salary_stat ON employees.department_id=emp_salary_stat.department_id
WHERE employees.department_id IS NOT NULL
ORDER BY employees.department_id

Thanks.

Wednesday, July 24, 2019 3:21 PM

All replies

• SELECT employees.last_name, employees.department_id, employees.salary, CAST(emp_salary_stat.avg, 100*(employees.salary-emp_salary_stat.avg)/employees.salary AS DECIMAL(20,2)) AS PercentDiff
FROM employees
INNER JOIN emp_salary_stat ON employees.department_id=emp_salary_stat.department_id
WHERE employees.department_id IS NOT NULL
ORDER BY employees.department_id

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Wednesday, July 24, 2019 3:24 PM
• Hi sqlnwb,

You can use FORMAT() function to achieve it.

Wednesday, July 24, 2019 3:24 PM
• ```SELECT employees.last_name, employees.department_id, employees.salary, emp_salary_stat.avg,
CAST(100.*(employees.salary-emp_salary_stat.avg)/employees.salary AS decimal(5,2))AS PercentDiff
FROM employees
INNER JOIN emp_salary_stat ON employees.department_id=emp_salary_stat.department_id
WHERE employees.department_id IS NOT NULL
ORDER BY employees.department_id```

Tom
Wednesday, July 24, 2019 3:26 PM
• ```SELECT employees.last_name, employees.department_id
, employees.salary, emp_salary_stat.avg,
Cast(100.*(employees.salary-emp_salary_stat.avg)/employees.salary as decimal(10,2)) AS PercentDiff
FROM employees
INNER JOIN emp_salary_stat ON employees.department_id=emp_salary_stat.department_id
WHERE employees.department_id IS NOT NULL
ORDER BY employees.department_id```

Wednesday, July 24, 2019 3:31 PM
• Hi,

SELECT employees.last_name, employees.department_id, employees.salary, emp_salary_stat.avg, round(100*(employees.salary-emp_salary_stat.avg)/employees.salary,2,1) AS PercentDiff
FROM employees
INNER JOIN emp_salary_stat ON employees.department_id=emp_salary_stat.department_id
WHERE employees.department_id IS NOT NULL
ORDER BY employees.department_id

Thursday, July 25, 2019 3:21 AM
• Hi sqlnwb,

Here it is:

```SELECT employees.last_name, employees.department_id, employees.salary, emp_salary_stat.avg,
convert ( decimal(7,2),100*(employees.salary-emp_salary_stat.avg)/employees.salary) AS PercentDiff
FROM employees
INNER JOIN emp_salary_stat ON employees.department_id=emp_salary_stat.department_id
WHERE employees.department_id IS NOT NULL
ORDER BY employees.department_id```

Regards,

Sabrina

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, July 25, 2019 5:51 AM