Sure, you can use distinct in a query. In your example:
select prod_id, count(distinct user_id), count(user_id)
from my_table
group by prod_id

would give you:
1 1 2

I don't think it will give you the result you are looking for, but then again, I can safely say I'm not sure what you are looking for.