troubleshooting Question

How to structure query with count aggregate

Avatar of ugeb
ugebFlag for United States of America asked on
4 Comments1 Solution277 ViewsLast Modified:
I'm working on a challenge on HackersRank, and brushing up on my SQL skills.  I'm given the following table 'Challenges':

Simple table with hackers and their challengesSo the challenge_id is unique in the table, and hacker_id is the id of the hacker who created the challenge.   I'm trying to meet the challenge step by step, so right now I'm working on what will be an inner query.  

What I'm trying to figure out is how to get a list of hacker_id's where there is no other hacker that created that exact number of challenges.  
Select c2.hacker_id, Count(c2.challenge_id) cnt2 
From Challenges as c2 
Group By c2.hacker_id 
Order By cnt2 Desc;
This query works in getting the number of challenges created by each hacker.  I don't really care about the order in this case, I just have the Order By for viewing intermediate results.

Next, I'm trying to get a list of EITHER all hackers who created a unique number of challenges or who DIDN'T create a unique number of challenges.  Either way works.  Playing around, I tried this:
Select *, count(ch.cnt2) cnt1 From 
(Select c2.hacker_id, Count(c2.challenge_id) cnt2 
From Challenges as c2 
Group By c2.hacker_id) ch, 
Challenges c1
Group By cnt1;
I received the error:
ERROR 1056 (42000) at line 1: Can't group on 'cnt1'

Why can't I group on cnt1?

I want the query to either include or exclude hackers without a unique number of challenges.  
Without changing the approach I'm taking, how can I accomplish this query?

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros