First, thank you for specifying that this is homework. We are limited in how much help we can give for homework, but this is for your benefit. I have know people to fail due to tutors calling direct help from these sites 'plagiarism'. Also, our approach is better for your education if you let us know :)
So: two issues. The second is easy once you have dealt with the first: The table has duplicates.
Duplicates are going to make your counts inaccurate. Any aggregate you attempt to use will be wrong. What you need is a clean table to group.
You need to create this 'clean' table in a query and then run the count query. Here you have several options:
Common Table Expressions:
My personal fav. They can be tricky to get your head around but are really efficient!
There are plenty of resources on this subject. Here's an article to get you started:
Common Table Expressions(CTE) in SQL SERVER 2008[
^]
Inline table query:
This is probably the easiest to implement. It just means that you define your table as a select before you use it. It might look something like this:
select count(*), groupid
from
(select distinct id, groupid from mytable)
group by groupid
Finally, you can create a temporary table to store your distinct data before you select from it. There are several ways of defining temporary tables:
create table #table(id int, groupid int) will be created in the tempdb
delcare @table table (id int, groupid int) will be created in memory only
etc...
This technique is very heavy but is sometimes the easiest to understand.
Look into each and pick your fav
Hope that helps ^_^