Changing data type to float and rounding to 2 decimal digits

Tables:

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.

My Attempts:

Attempt 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

Attempt 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

Attempt 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

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

Answer

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).

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

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

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

Advertisement

Answer