'How to query how many different IDs use the same column value?

I have this homework assignment where I'm attempting to query a table to find the id numbers that are all using the same column value, let's say last name in this case. I'd like to find the ids that use the same last name more than once, and have a column that tells me the total number of unique IDs that used that same last name.

SELECT id, COUNT(*) as ID_count 
    FROM [table] 
    WHERE l_name IN
    (
        SELECT l_name 
        FROM [table]
        GROUP BY l_name HAVING COUNT(*)>1
    )
GROUP BY id;

This is what I have so far. It grants me the ID number, but the count(*) is not what I'm going for. What I'm instead trying to get is how many unique IDs have "Smith" as their last name, instead of all the occurrences of one specific ID that has used "Smith".

I've tried different things but I feel like I'm at a roadblock. Any hints or tips are nice; I don't need this problem solved 100%, but I feel as if I can't past the idea of using count(*).

Thanks all.

sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source