CODEWITHSUNDEEP
t-sql
Jerreck
Jerreck

Reputation: 3010

SQL Server SUM() for DISTINCT records

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?

Upvotes: 6

Views: 88547

Answers (8)

Haitham Othman
Haitham Othman

Reputation: 61

May be a duplicate to Trying to sum distinct values SQL

As per Declan_K's answer:

Get the distinct list first...

SELECT SUM(SQ.COST)
FROM 
(SELECT DISTINCT [Tracking #] as TRACK,[Ship Cost] as COST FROM YourTable) SQ

Upvotes: 0

Graham Laight
Graham Laight

Reputation: 4840

If circumstances make it difficult to weave a "distinct" into the sum clause, it will usually be possible to add an extra "where" clause to the entire query - something like:

select sum(t.ColToSum)
from SomeTable t
where (select count(*) from SomeTable t1 where t1.ColToSum = t.ColToSum and t1.ID < t.ID) = 0

Upvotes: 0

FistOfFury
FistOfFury

Reputation: 7145

You can see for yourself that distinct works with the following example. Here I create a subquery with duplicate values, then I do a sum distinct on those values. 

select DistinctSum=sum(distinct x), RegularSum=Sum(x)
from
(

    select x=1
    union All
    select 1
    union All
    select 2
    union All
    select 2

) x

You can see that the distinct sum column returns 3 and the regular sum returns 6 in this example.

Upvotes: 3

Max Zhuravlev
Max Zhuravlev

Reputation: 334

SUM(DISTINCTROW table_name.something)

It worked for me (innodb).

Description - "DISTINCTROW omits data based on entire duplicate records, not just duplicate fields." http://office.microsoft.com/en-001/access-help/all-distinct-distinctrow-top-predicates-HA001231351.aspx

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

;WITH cte
as
  (
  SELECT table_name.users , rn = ROW_NUMBER() OVER (PARTITION BY users  ORDER BY users) 
          FROM table_name
  )
SELECT SUM(users)
FROM cte 
WHERE rn = 1

SQL Fiddle

Try here yourself
TEST

DECLARE @table_name  Table (Users INT );
INSERT INTO @table_name Values (1),(1),(1),(3),(3),(5),(5);

;WITH cte
as
  (
  SELECT users , rn = ROW_NUMBER() OVER (PARTITION BY users  ORDER BY users)
     FROM @table_name
  )
SELECT SUM(users) DisSum
FROM cte 
WHERE rn = 1

Result

DisSum
9

Upvotes: 0

Kyle Hale
Kyle Hale

Reputation: 8120

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.

Upvotes: 5

Fabian
Fabian

Reputation: 2972

You can use a sub-query:

select sum(users)
from (select distinct users from table_name);

Upvotes: 2

juergen d
juergen d

Reputation: 204766

Use count()

SELECT count(DISTINCT table_name.users)
FROM table_name

SQLFiddle demo

Upvotes: 6

Related Questions