I keep getting errors when working on this query. The closest I think I got, still returned ORA-00933: SQL command not properly ended

Question

I am learning SQL and can not figure out what I am doing wrong in my code.

Select robot, city, country, count(sponsor.sponsor)
    From robot join sponsor on robot.robotid = sponsor.robotid
        Group by robotid
Where country not in ('United States');

I am supposed to find all robots not from the US and how many sponsors they have. I have figured out how to get only the international robots from the robot table and the total number of sponsors each robotID has in the sponsor table but can not figure out how to join them. If it helps the pk for both tables is robotID but that value should not be returned in the query.

Answer 1

There are multiple issues with the query. Three that are obvious:

• The select columns are not compatible with the group by columns.
• The where clause is misplaced.
• robotid is ambiguous

One could guess that you intend:

Select r.robot, city, country, count(*)
From robot r join
     sponsor s
     on r.robotid = s.robotid
where country not in ('United States')
group by r.robot, city, country;

This is just a guess. It should fix the syntax problems, but it may not be what you want. Why not? You have not explained your problem, provided sample data, or desired results.

Answer 2

Check this out.

Select robot, city, country, count(sponsor.sponsor)
From robot join sponsor on robot.robotid = sponsor.robotid
Where country not in ('United States')
Group by robotid;