none
Limit result of query to 2 decimal places RRS feed

  • 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 Answered "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
    Moderator
  • 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