Why is my CAST not casting to float with 2 decimal places?

Question

This is being tested with PostgreSQL 9.6 - it's a SQL challenge on the CodeWars website.

https://www.codewars.com/kata/580fb94e12b34dd1c40001f0/train/sql

SELECT
  job_title,
  CAST(SUM(salary)/COUNT(salary) AS DECIMAL(10,2)) AS average_salary,
  COUNT(people_id) AS total_people,
  CAST(SUM(salary) AS DECIMAL(10,2)) AS total_salary
FROM job
GROUP BY job_title
ORDER BY average_salary DESC;

average_salary 74.4506 --> should be 74.45
total_salary 372.253 --> should be 372.25

Tag your question with the database you are using. – Gordon Linoff Dec 17 '20 at 02:13 — Gordon Linoff, Dec 17 '20 at 02:13

score 1 · Answer 1 · answered Dec 17 '20 at 02:12

1

My suspicion is that your database does integer division and salary is an integer. I would suggest a simple fix:

CAST(SUM(salary) * 1.0 / COUNT(salary) AS FLOAT)

You don't really need the additional cast() to a flat, but I left it in.

If you want exactly two decimal places, cast to a decimal/numeric with the appropriate precision:

CAST(SUM(salary) * 1.0 / COUNT(salary) AS DECIMAL(10, 2))

answered Dec 17 '20 at 02:12

Gordon Linoff

1,242,037
58
646
786

Thank you for your suggestion. I tried that, and weirdly it still gives me the same result. I've been wrecking my brain on this... I'm testing through CodeWars, so wondering if there's something wrong on their side... Very strange. – Cristina Sahoo Dec 17 '20 at 02:18

Yuyanto · Accepted Answer · 2020-12-17T05:33:50.727

0

try this.

ROUND(SUM(salary) / COUNT(salary), 2)::FLOAT AS average_salary

edited Dec 17 '20 at 05:33

answered Dec 17 '20 at 02:16

Yuyanto

121
9

Thank you for answering. CONVERT returns an error, something it does not like about the comma before the CAST inside the CONVERT... There was an error with the SQL query: PG::SyntaxError: ERROR: syntax error at or near "," LINE 6: CONVERT(decimal(10,2), CAST(SUM(salary)/COUNT(salary) AS D... – Cristina Sahoo Dec 17 '20 at 02:22
my apologize.. should have asked you the database engine you are using. from the error seems like you are running postgresql try this link and see whether it works for you https://stackoverflow.com/questions/55426801/changing-data-type-to-float-and-rounding-to-2-decimal-digits – Yuyanto Dec 17 '20 at 02:31
Thank you. The ROUND function, then ::FLOAT worked!! You're awesome. :-) – Cristina Sahoo Dec 17 '20 at 02:35
Hi, would appreciate if you can upvote the answer :) – Yuyanto Dec 17 '20 at 05:33
I checked and voted.. but it says my vote doesn't count.. maybe because I'm too new.. I just joined two days ago. – Cristina Sahoo Dec 18 '20 at 04:14

score 0 · Answer 3 · answered Dec 17 '20 at 02:39

For anybody running into the same issue...

The code was in PostgreSQL 9.6, and below code worked as it should have. Thank you to Yuyanto for finding the answer.

SELECT
job_title
,ROUND(SUM(salary) / COUNT(salary), 2)::FLOAT AS average_salary
,COUNT(people_id) AS total_people
,ROUND(SUM(salary), 2)::FLOAT AS total_salary
FROM job
GROUP BY job_title
ORDER BY average_salary DESC; <br

Why is my CAST not casting to float with 2 decimal places?

3 Answers3