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?

score 6 · Accepted Answer · answered Nov 15 '13 at 20:52

6

Use count()

SELECT count(DISTINCT table_name.users)
FROM table_name

SQLFiddle demo

answered Nov 15 '13 at 20:52

juergen d

201,996
37
293
362

13

COUNT() and SUM() aren't the same thing at all. (I realize you think he wants a count of Users, but Users might actually be a number, you know.) – Kyle Hale Nov 15 '13 at 20:54
I'm not familiar enough with SQL to know if it's the 'appropriate' technique, but that's really simple and it works perfectly. Thank you :) – Jerreck Nov 15 '13 at 20:59
1

I did some research, and have decided that this is the best solution to my problem. In regard to @KyleHale 's comment, the data type of the Users field is string, and I was trying to select the distinct records from that field. Since the COUNT aggregate function returns a count of all the records in a field, and the DISTINCT keyword selects only unique records, the combination of COUNT(DISTINCT table_name.users) returned the exact result I expected - the total number of distinct records from the Users field. Again, thank you all for your help :) – Jerreck Nov 19 '13 at 15:26

score 5 · Answer 2 · answered Nov 15 '13 at 20:56

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.

score 3 · Answer 3 · answered Oct 10 '14 at 14:11

3

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.

answered Oct 10 '14 at 14:11

FistOfFury

6,735
7
49
57

but this only works if the value is the "index" to make the record unique. What if the value is not unique per record based on another ID value? – Fandango68 Feb 16 '22 at 03:48

score 2 · Answer 4 · answered Nov 15 '13 at 20:52

2

You can use a sub-query:

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

answered Nov 15 '13 at 20:52

Fabian

2,822
1
17
22

1

This is not true at all, there's been a SUM(DISTINCT) since SQL Server 2000. see http://technet.microsoft.com/en-us/library/ms187810.aspx – Kyle Hale Nov 15 '13 at 20:57
1

Yes, I just checked that. I have removed that statement. It does not exists for SUM in standard SQL AFAIK, only for COUNT. – Fabian Nov 15 '13 at 20:57
This does not work. In order for it to work you need to declare the ID for the result. – markthewizard1234 Mar 03 '16 at 12:18
1

Eg/ Select * From (SELECT DISTINCT field, field2 FROM table) X – markthewizard1234 Mar 03 '16 at 12:20

score 1 · Answer 5 · answered Jul 28 '14 at 04:35

1

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

answered Jul 28 '14 at 04:35

Max Zhuravlev

324
3
13

5

The OP specified TSQL in the question (which only works on Microsoft SQL Server and Sybase), and you've said your answer applies to Innodb (which is one of the possible MySQL query engines) and you've provided a link to an article that applies to Microsoft Access (which definitely has nothing to do with MySQL or the original TSQL question). – Davos Feb 18 '16 at 03:05
In MySQL, [`DISTINCTROW` is a synonym for `DISTINCT`](https://dev.mysql.com/doc/refman/5.5/en/select.html). In SQL Server, it doesn't seem to exist at all. I haven't tested in Access. – Álvaro González Nov 27 '17 at 13:07

M.Ali · Answer 6 · 2013-11-15T21:11:25.097

;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

score 0 · Answer 7 · answered Nov 21 '19 at 12:21

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

score 0 · Answer 8 · edited Sep 27 '20 at 11:02

0

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

edited Sep 27 '20 at 11:02

fcdt

2,371
5
14
26

answered Sep 27 '20 at 10:38

Haitham Othman

61
1
3

SQL Server SUM() for DISTINCT records

8 Answers8

SQLFiddle demo