6 kyu
SQL Bug Fixing: Fix the JOIN
9,417matt c
Loading description...
SQL
Fundamentals
This comment has been reported as {{ abuseKindText }}.
Show
This comment has been hidden. You can view it now .
This comment can not be viewed.
- |
- Reply
- Edit
- View Solution
- Expand 1 Reply Expand {{ comments?.length }} replies
- Collapse
- Spoiler
- Remove
- Remove comment & replies
- Report
{{ fetchSolutionsError }}
-
-
Your rendered github-flavored markdown will appear here.
-
Label this discussion...
-
No Label
Keep the comment unlabeled if none of the below applies.
-
Issue
Use the issue label when reporting problems with the kata.
Be sure to explain the problem clearly and include the steps to reproduce. -
Suggestion
Use the suggestion label if you have feedback on how this kata can be improved.
-
Question
Use the question label if you have questions and/or need help solving the kata.
Don't forget to mention the language you're using, and mark as having spoiler if you include your solution.
-
No Label
- Cancel
Commenting is not allowed on this discussion
You cannot view this solution
There is no solution to show
Please sign in or sign up to leave a comment.
Here is my poem for this exercise:
-- I thought I could round with ROUND(),
-- I even thought I could cast something with CAST(),
-- but only trunc() helped me before I truncated, while I was progressing!
-- The decimals danced away, ROUND couldn’t stay,
-- But trunc() stepped in, leading the way!
Average and total values are the same in this dataset, as every job has only 1 person. Sorting should be done descending by highest average salary. But the cashier is expected to be the last/ the one with the least salary of 14386.49, even though the astronomer is only receiving a measly salary of 10266.25.
Edit
Nevermind, my bad!
This comment has been hidden.
This comment has been hidden.
was stuck on this problem for days........
first thing i learnt is that round() needs a numeric input and integer for dp. i.e round(x,y), x must be numeric and y must be integer.
second thing i learnt is that the rounding changes only apply once converted to float afterwards.. confusing. i.e round(x,y)::float will then show the rounded number as however decimal places.
Had the issue of output detected 11 decimals when <= 2 was asked on testing. But this issue does not appear on submitting.
Receieved only on submitting and not appeared on testing for me
Thank you for this kata. It is always challenging to deal with float numbers and decimal points in POSTGRES. It took me a while to finally round the numbers while casting them as float types.
My kata passes when I test it but it fails on the attempt.Does anyone have any idea why? This is the error that I get : expected: [{:average_salary=>98677.2, :job_title=>"engineer", :total_people=>1, :total_salary=>98677.2}, {:aver...1}, {:average_salary=>10252.36, :job_title=>"economist", :total_people=>1, :total_salary=>10252.36}] got: [{:average_salary=>82058.12, :job_title=>"lifeguard", :total_people=>4, :total_salary=>328232.49}, {:...1}, {:average_salary=>10252.36, :job_title=>"economist", :total_people=>1, :total_salary=>10252.36}]
Did you change the order by statement? Got a similar error and it was resolved after i cahnged the order by to average_salary.
omg why is it so difficult to get a result with 2 decimal places. Converting to float results in 3 decimal places, ROUND, TRUNC, FORMAT are not accepted. I have also tried CAST and CONVERT with decimal and numerical. how do I get exactly 2?
CAST and CONVERT work, but than you have to turn the scientific notation into a float. Example: CAST(AVG(j.salary), DECIMAL(10,2))::FLOAT
This comment has been hidden.
For some weird reason CAST AS FLOAT gives more than 2 decimal places for some outputs.
I ended up learning something new today. Once you get hold of the casting, it just flows
round(avg(salary), 2) did not appear to work, which tripped me up. Whats going on is it is doing the round you want, but the numeric format is weird and it is displaying it in a really weird way. You need to convert it to the format that they tell you to, which doesn't display it in a weird way after you convert the numeric salary column.
This comment has been hidden.
Easier to rewrite the query than fix the freaking code.
it's a bit tricky this one, but one needs to pay attention to the relationship between the function round and what datatype it takes on. Then do a final conversion.
expected: [{:average_salary=>99886.55, :job_title=>"mechanic", :total_people=>1, :total_salary=>99886.55}, {:av....29}, {:average_salary=>12786.66, :job_title=>"painter", :total_people=>1, :total_salary=>12786.66}] got: [{:average_salary=>99886.55, :job_title=>"mechanic", :total_people=>1, :total_salary=>99886.55}, {:av....29}, {:average_salary=>12786.66, :job_title=>"painter", :total_people=>1, :total_salary=>12786.66}]
That's all.
Error: total_salary should be 2 or less decimal places expected: <= 2 got: 12
job_title average_salary total_people total_salary tailor 15900.29 1 15900.29 actor 17954.17 1 17954.17 factory worker 19717.98 1 19717.98 doctor 21722.36 1 21722.36 businessman 23037.04 1 23037.04 lawyer 27057.47 1 27057.47 engineer 30808.39 1 30808.39 receptionist 35000.08 1 35000.08 secretary 39797.21 1 39797.21 fisherman 40295.4 1 40295.4 butcher 41223.13 1 41223.13 gardener 41857.21 1 41857.21 police officer 47045.42 1 47045.42 artist 47755.41 1 47755.41 shop assistant 27663.36 2 55326.72 firefighter 59212.62 1 59212.62 cashier 60425.93 1 60425.93 air traffic controller 63211.79 1 63211.79 attorney at law 65427.96 1 65427.96 waiter 70950.09 1 70950.09 bartender 71742.92 1 71742.92 translator 73089.47 1 73089.47 model 24517.37 3 73552.12 statistician 76844.82 1 76844.82 mechanic 39000.95 2 78001.91 actress 79604.24 1 79604.24 soldier 84446.22 1 84446.22 programmer 85284.75 1 85284.75 newsreader 42697.26 2 85394.52 writer 86959.81 1 86959.81 hairdresser 29945.77 3 89837.32 architect 91175.88 1 91175.88 bookkeeper 53904.55 2 107809.1 judge 54984.5 2 109969 coach 56126.13 2 112252.26 nurse 57505.69 2 115011.39 economist 61333.53 2 122667.06 interpreter 62317.38 2 124634.77 banker 62665.09 2 125330.17 librarian 64190.55 2 128381.11 traffic warden 66440.38 2 132880.77 advocate 45191.47 3 135574.4 optician 71266.32 2 142532.64 teacher 71551.3 2 143102.6 farmer 75535.01 2 151070.02 lifeguard 52054.48 3 156163.45 photographer 78222.95 2 156445.9 carpenter 81509.43 2 163018.86 biologist 54818.24 3 164454.73 philosopher 57412.12 3 172236.38 veterinary doctor 60126.23 3 180378.7 dentist 46161.26 4 184645.05 window cleaner 48197.38 4 192789.52 builder 68239.43 3 204718.3 dietician 69915.12 3 209745.34 taxi driver 87674.31 4 350697.22
Even when I hard code a value I get the same error
Error: total_salary should be 2 or less decimal places expected: <= 2 got: 16
job_title average_salary total_people total_salary tailor 2.22 2 2.22 air traffic controller 2.22 2 2.22 photographer 2.22 2 2.22 plumber 2.22 2 2.22 agriculturist 2.22 3 2.22 taxi driver 2.22 1 2.22 programmer 2.22 2 2.22 firefighter 2.22 2 2.22 coach 2.22 1 2.22 chef 2.22 1 2.22 engineer 2.22 2 2.22 actor 2.22 2 2.22 model 2.22 1 2.22 scientist 2.22 1 2.22 painter 2.22 1 2.22 veterinary doctor 2.22 1 2.22 cashier 2.22 4 2.22 bartender 2.22 1 2.22 dietician 2.22 1 2.22 soldier 2.22 1 2.22 journalist 2.22 1 2.22 postman 2.22 1 2.22 designer 2.22 1 2.22 lawyer 2.22 1 2.22 statistician 2.22 1 2.22 travel agent 2.22 1 2.22 newsreader 2.22 1 2.22 lifeguard 2.22 1 2.22 dental hygienist 2.22 1 2.22 builder 2.22 3 2.22 factory worker 2.22 4 2.22 shop assistant 2.22 2 2.22 pilot 2.22 2 2.22 lecturer 2.22 1 2.22 interpreter 2.22 1 2.22 advocate 2.22 1 2.22 attorney at law 2.22 1 2.22 musician 2.22 1 2.22 hairdresser 2.22 1 2.22 businessman 2.22 1 2.22 butcher 2.22 2 2.22 librarian 2.22 3 2.22 chemist 2.22 2 2.22 farmer 2.22 2 2.22 dentist 2.22 4 2.22 writer 2.22 2 2.22 developer 2.22 1 2.22 accountant 2.22 2 2.22 barber 2.22 1 2.22 traffic warden 2.22 2 2.22 carpenter 2.22 3 2.22 economist 2.22 1 2.22 bookkeeper 2.22 1 2.22 banker 2.22 1 2.22 astronomer 2.22 2 2.22 judge 2.22 3 2.22 fisherman 2.22 3 2.22 philosopher 2.22 2 2.22 optician 2.22 1 2.22 translator 2.22 1 2.22 editor 2.22 1 2.22
This comment has been hidden.
Make sure to cast results to a float
This comment has been hidden.
This comment has been hidden.
Numbers are big, and are exressed in exponential form. For me, type conversion (required) solved the problem.
This comment has been hidden.
not an issue
It seems to me that both the tests and the submission don't check for the requirement, resulting table should be ordered by average salary descending.
Why do so many solutions have a join in them? All the relevant information was in the job table. Am I missing something here?
It depends how you tackle a "refactoring" kata. Either you make a minimum number of changes to make it work, or you take a step back and rewrite the thing completely. Looking at the solutions, I'd say most users (including myself) picked the former approach. Is that a bad thing .. I don't know
This comment has been hidden.
Please mark your post as having spoiler content next time.
Because there isn't round function for floats. Round() require the 'numeric' type in Postgresql
Do you have some advice on how to approach error handling in SQL Katas?
I did a lot of Katas in python. With print()-Function and IDE I can look for mistakes in my code easily, but in SQL I am doing to much trial and error.
No idea if anyone ran into this issue, but sometimes when testing the solution to this kata the test will fail stating that "total_salary does not have 2 decimals" despite all values in the table fulfil this requierement.
And when skipping the test completely and running the attempt this does not happen! Maybe is a bug with the test code.
I had the same problem
Same here. The table seems to show 2 decimal places all the way down.
I'm struggling to understand why my test code passes but fails the attempt, especially when the numbers that I can see match the expected output? Help!
Did you check if your ORDER BY is as the exercises requires?
This comment has been hidden.
How to roundoff for 2 dp. ROUND() giving an error.
Think about rounding it as a different datatype, or try using ROUND() to round to an integer.
I am having the same issue. How did you address it?
Also, isn't it supposed to be a float?
It looks like need to sort by total salary also. Really not sure why!
Fun SQL katas!
I suggest adding a test to check the ORDER BY. Otherwise it's crystal clear what is working and not.
I do everything as possible to made this kata, passed the first test and when i try to do the attemp this fail, and the results are the same that the output, i hope you can fixed, thanks.
I don't really get why this kata named '...JOIN'
This comment has been hidden.
Test Failedexpected 513.01 (#BigDecimal:55970764d9c8,'0.51301E3',18(18)) to be a kind of Float --> how to get float successfully in postgres 1 test case fail due to float cast( (SUM(j.salary) / COUNT(p)) as float(2) ) as average_salary numeric is failing
This comment has been hidden.
Is the expected solution really ordered by the total_salary?? hmm..
Actually, by the average salary.
Whenever I click "Run sample tests" I get different results - without changing anything in my code. I always pass all the tests but then my solution gets rejected because of wrong values. Any suggestions what I can do about that?
Same here. Did you find a solution?
in my case it was wrong ordering.
This comment has been hidden.
Looks good to me. What did the message say?
I do not know. For some resason it seems there is a slight difference in the order of rounding and typecasting application.
Every value rounded to 2 decimals and still not passing the test. What's going on? :(
Deleted the semicolon and it works. Semicolons are really a powerful weapons in CodeWars. :P
This comment has been hidden.
In version 2, don't forget to wrap the whole field when converting to a float..
e.g.
(trunc(SUM(J.salary)*100)/100)::floatStill getting the error total_salary should be 2 or less decimal places yet when I look at the out put results it does have the correct results.
OP solved it, closing
Submitted a query, it didn't pass; waited 10 minutes, resubmitted and it worked. wtf?
lol! CW can be buggy
Do you want to paste your solution and mark it as a spoiler just incase there is something wrong with the kata itself?
This comment has been hidden.
OP solved it, closing
Why wouldn't the TRUNC() function work?
Something weird with rounding, some attempts fail and some attempts pass. Looks like test cases use bank rounding
Same here, every value rounded to 2 decimals and still not passing the round test. :/
This comment has been hidden.
Not a suggestion.
This comment has been hidden.
This comment has been hidden.
it'll be with the casting.. try
round(CAST(SUM(j.salary) / COUNT(p) as float))::FLOATthanks. You pointed me in the right direction and finally solved it. Onwards!
OP solved it, closing
Great Kata; took me forever to figure out. Bad news is it took me like three hours, as I know SQLite and not POSTgreSQL. Good news is I learned a lot about the differences (I think.)
That's great! Good job :)
Am I missing something or the salaries are a percetage of something? When I select * from job, I get salaries like 0.253123333333333333E2, which does not look like a salary at all.
It's in scientific notation. The number you wrote would be 25.31233... which you can assume is thousands of dollars per year i guess
This comment has been hidden.
odd - went back and tried it again (no code changes) and then started passing. Suspicious.
Checked it few times, had same effect. Seems that decimals test works properly only after few tries (maybe it was second try)
Mine failed twice, then succeeded on the third try. The error said I had 14 decimal placed in my column, when the output was clearly showing 2.
There are a ton of solutions on here that are exposed to divide by zero errors. Code stability must be a measurable variable in terms of correct results.
This comment has been hidden.
Same problem happened to me. Took 3 tries to pass the test using ROUND(numeric, int)::float
Not sure if others have commented on this already (looks like they didn't from scanning the discourse) but it would be helpful if the kata stated that it was ok to round numbers. I thought about using round but thought it wasn't correct and ended up using an ugly double conversion.
Otherwise, I enjoy Timmy's mishaps :)
This comment has been hidden.
CAST(123.454 AS FLOAT)
This comment has been hidden.
This comment has been hidden.
This comment has been hidden.
This comment has been hidden.
You souldn't have a join at all. There's no reason to join tables that you're not pulling data from.
The phrases "total average salary" and "total people" are confusing, if not wrong. I suggest rephrasing as "average salary" and "applicant count".
I don't see an issue with that if you could elaborate but with 400+ completed making that change would invalidate a lot of solutions.
as a non-native speaker it was hard for me to understand too. My wife is an editor for a NYC ad agency and she said total people means count and total salary means the sum here but she agreed it wasn't worded clearly. But I understand why the kata can't be changed in hindsight.
very puzzled at casting, somewhat
This comment has been hidden.
It would be nice if you included the error, but start by removing the distinct.
The problem is that the query 'join' in your code has not been fixed. The condition for joining is missing. Perhaps this helps: https://www.postgresql.org/docs/8.3/static/tutorial-join.html
The test based on diff'ing the output can fail if there are multiple jobs with identical salarys
you should specify the descending order in the description
it's apart of the kata to infer that from the epexcted results because this is a bug fixing kata.
This comment has been hidden.
The scientfic notation seems to be generated by Sequel and not by PostgreSql. Imho this should not be tested.
You can convert to a float and control the amount of decimal places.
It looks like in this particular case JOIN isn't necessary at all. Therefore either the assignment should be changed to justify the JOIN or Kata should be renamed.
well timmy's not perfect and this is a bug fixing kata so you're not required to change much.
Additionally, my solution was accepted without containing any join at all. Perhaps a test case to validate the presence of a join clause would solve that?
This comment has been hidden.
you need to
COUNTthe people properly. round theaverage_salaryandtotal_salaryto 2 decimal places andORDER BYtheaverage_salarydecending.This comment has been hidden.
oh, I missed that because there isn't an explicit test for ordering by average_salarys but I will add that to the description.