SQL Server SUM() for DISTINCT records

Question

I have a field called "Users", and I want to run SUM() on that field that returns the sum of all DISTINCT records. I thought that this would work:

SELECT SUM(DISTINCT table_name.users)
FROM table_name

But it's not selecting DISTINCT records, it's just running as if I had run SUM(table_name.users).

What would I have to do to add only the distinct records from this field?

Answer 1

Use count()

SELECT count(DISTINCT table_name.users)
FROM table_name

SQLFiddle demo

Answer 2

This code seems to indicate sum(distinct ) and sum() return different values.

with t as (
select 1 as a 
union all
select '1'
union all
select '2'
union all
select '4'
)

select sum(distinct a) as DistinctSum, sum(a) as allSum, count(distinct a) as distinctCount, count(a) as allCount from t

Do you actually have non-distinct values?

select count(1), users
from table_name
group by users
having count(1) > 1

If not, the sums will be identical.