将数据类型更改为浮点并四舍五入为2个十进制数字 [英] Changing data type to float and rounding to 2 decimal digits

问题描述

表格:

people(id, name)
job (id, people_id, job_title, salary) 

目标:显示每个唯一的工作，总平均工资(浮动并四舍五入到小数点后两位)人员和总工资(浮动并四舍五入到小数点后两位)，并按最高平均工资排序.

Goal: Display each unique job, the total average salary (FLOAT and rounded to 2 decimal places), the total people and the total salary (Float and rounded to 2 decimal places) and order by highest average salary.

因此，挑战在于将类型转换类型保留为浮点型，同时将其四舍五入到小数点后两位.

So the challenge is to keep the cast type as float while rounding it to 2 decimal places.

我已经到了将它四舍五入到小数点后两位的位置，但是它不是浮点数.我已经将它放到浮动的位置，但是我无法将其四舍五入到小数点后两位.

I've gotten to where I've rounded it 2 decimal places but it's not float. I've gotten it to where it's float but I can't round it to 2 decimal places.

我的尝试:

尝试1:

SELECT 
  distinct(j.job_title) as job_title,
  to_char(AVG(j.salary)::FLOAT, 'FM999999990.00') as average_salary,
  COUNT(p.id) as total_people,
  CAST (SUM(j.salary) AS FLOAT) as total_salary
  FROM people p
    JOIN job j on p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary

问题:仍然说它不是浮动的

Problem: Still says it's not float

尝试2:

SELECT 
  distinct(j.job_title) as job_title,
  CAST (AVG(j.salary) AS FLOAT) as average_salary,
  COUNT(p.id) as total_people,
  CAST (SUM(j.salary) AS FLOAT) as total_salary
  FROM people p
    JOIN job j on p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary

问题:未四舍五入到小数点后两位

Problem: not rounded to 2 decimal places

尝试3:

SELECT 
  distinct(j.job_title) as job_title,
  ROUND (AVG(CAST(j.salary as FLOAT)), 2)) as average_salary,
  COUNT(p.id),
  ROUND (SUM(CAST(j.salary as FLOAT)), 2)) as total_salary
  FROM people p
    JOIN job j on p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary

我收到一条错误消息，说我需要添加显式强制类型，这导致我尝试编号1.

I get an error saying I need to add explicit cast types which led me to attempt number 1.

推荐答案

答案取决于列 salary 的实际数据类型.关键是Postgres中的 round()不允许 float (仅支持 numeric 类型).

The answer depends on the actual datatype of column salary. The key point is that round() in Postgres does not allows floats (only numeric types are supported).

如果要处理数字数据类型，则可以首先 round()，然后强制转换为 float :

If you are dealing with a numeric datatype, then you can first round(), then cast to float:

round(avg(salary), 2)::float

如果要处理 float 列，则需要在其上使用 round()之前强制转换聚合函数的结果:

If you are dealing with a float column, then you would need to cast the result of the aggregate function before using round() on it:

round(avg(salary)::numeric, 2)::float

这篇关于将数据类型更改为浮点并四舍五入为2个十进制数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！