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