7 kyu
SQL Basics: Simple JOIN with COUNT
20,327matt c
Loading description...
SQL
Databases
Fundamentals
.avif)
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.
This was a horribly written question. Given there is no opportunity for follow on questions the question needs to be rewritten, especially as the SQL syntax to solve is not actually very complicated.
Additionally at least some sort of idea what the expected result set looks like on an apparently "simple" problem would be helpful as this is supposed to be about learning and practice.
0/10 would not recommend.
The kata was good but the fact that the test fail if you don't return the id is diabolical! I smash my head 10 minutus figuring out what i was doing wrong and in the end it was the fact that i was returning only the people names omitting the id.
And what's the issue? It's stated in the description:
That includes id.
Good challenge, but the description is not very clear. Here's how I think it could be rewritten:
Presented below is a schema relating toys to a toy factory.
Table "toy_factory"
Table "toys"
Write a query that returns all the fields in
toy_factory, counts the number of toys each factory has made (return the column as 'toy_count'), and groups these counts by the corresponding factory.It could make more sense, but the current tables names are different, and changing them now would render all solutions invalid, won't happen, sorry.
why does it not matter when you count using the id or name column in the toys table?
great kata
I'm curious why I run the simple query (SELECT * FROM people) and every time I run it the result is two rows with (seemingly) random names each time.
It uses Faker (https://github.com/faker-ruby/faker) to create fake data.
ALL YOU NEED RETURN TABLES WITH COLUMNS people.id, people.name count, to count (this is aggfunc)
not perfect, description missing important detail
TIP: last column of the result table needs to be named toy_count
It's already there.
my bad
Great but should be explained better as the table content is not quite clear. You should say that toys are linked to a factory by people_id, so we don't have to run a test command to see the content of the toys table and the people table, and that we want to count the number of toys for each people.
It was a good one though
I actually loved it. It made me refresh my attention to detail.
This site's sql exercises are horrible. I'm going elsewhere
people_id from toys table is the same as id from people table. Figuring this out (by selecting the output of both tables for comparison) helped me out a great deal.
it's never suggested that we should return for each name so i assumed we should do a window count... it's never stated we should group by each name..come on
This comment has been hidden.
Why in group by I can use only people id and work? What happend with people.name?
has to do with grouping by primary key, seems like a postgre-sql specific feature: https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b
Great kata!
You need a test case where there is no row in toys for the people_id. I didn't handle for this edge case, and my code still passed. Of course, maybe this shouldn't be at such a low level if it uses NVL. Maybe clarify the assumption that there will always be a record in toys.
Thank you @matt c for this Kata. Took me out of my comfort zone. Good error messages, helped me alot.
Maybe it's because of the SQL software used, but SQL Server would scream if you didn't group by all your select fields.
Edit: A lot of the solutions don't group by all fields selected.
Why if i query people table one by one coloumn its not work, and if use * its work. altough the result is same just different in query.
Not an issue, a question (as you stated it ;p ). Since we do not see your code, we cannot really tell. But for sure, both approaches are working (just look at the solutions page).
cheers
需要注意的是最后要求每个人对应的玩具数量，里面的逻辑是要求分组的
I get a failed result because the test expected "Kihn-Tillman" but got: nil. But I have that entry under names, it is not nill. Why is the test registering a nil value when the names entry is populated with the test target value? Test results below.
Test Results: SELECT Results id names toy_count 2 Kihn-Tillman 6 1 Kuhn-Wolff 4 items should return 2 items should return names Test Failed expected: "Kihn-Tillman" got: nil
The same thing. Is there any explanation about that?
Sorry! Guys who came across with such a problem, check the name of your return columns.
If you provide a name for the second column, it should be "name". Otherwise, this error appears.
This comment has been hidden.
Are you sure about that?
You are right. My fault
Add correct description, like 'you need to return these columns: id, name, toy_count'.
thx
This comment has been hidden.
mb you don't add the id column for people?
This comment has been hidden.
This comment has been hidden.
This comment has been hidden.
removed.
I still got this error when attempting the kata today - eventually figured out that it needed to be grouped.
This comment has been hidden.
This comment has been hidden.
This comment has been hidden.
oh thanks a lot! I will add that check in, I've never used ruby in my life so still a lot to learn :P
added that conditional check in the final test cases.
Great kata. Thanks. :-)
Please more of it! :-)
already got some new ones