MySQL - SUM of rows excluding duplicates

Question

I have a table as follows

+----+---------+-------------+--------+
| id | user_id | ip_address  | amount |
+----+---------+-------------+--------+
|  1 |       2 | 192.168.1.1 | 0.5    |
|  2 |       2 | 192.168.1.1 | 0.5    |
|  3 |       2 | 192.168.1.2 | 0.5    |
|  4 |       2 | 192.168.1.3 | 0.5    |
+----+---------+-------------+--------+

I want to only unique ip_address SUM of amount and count duplicates as only 1.

From above table there are 4 rows from which first 2 IP addresses are same but I want to count it only one.

I am trying to SUM of amount field like this.

SELECT SUM(amount) AS total_amount FROM table

I am getting 2 as output.

But I want 1.5 as output, can you please help me regarding this issue.

One option (and probably the best) is to do a `SUM()` over a subquery with `DISTINCt` in it. — Wolph, Apr 27 '15 at 05:31

score 2 · Accepted Answer · answered Apr 27 '15 at 05:32

2

The best approach is to aggregate by IP address first and then re-aggregate:

SELECT SUM(amount) AS total_amount
FROM (SELECT ip_address, MAX(amount) as amount
      FROM table
      GROUP BY ip_address
     ) t

answered Apr 27 '15 at 05:32

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

The same can be achieved by the use of DISTINCT as well. It is a bit faster compared to GROUPBY provided you do not have any indexing on the ipaddr field

checkout this link- What's faster, SELECT DISTINCT or GROUP BY in MySQL?

SELECT SUM(amt) FROM (SELECT DISTINCT ipaddr,amt FROM ipaddr_sum) t

enter image description here

Now the above difference was for just 4 rows. Imagine if the table has lakhs of rows, it would definitely be helpful (NOTE : Provided youdo not have indexing in your ipaddr field )

edited May 23 '17 at 11:58

Community

1
1

answered Apr 27 '15 at 06:34

Abhinav

8,028
12
48
89

The linked answer is out of date. I defy you to construct a scenario in which DISTINCT outperforms GROUP BY. – Strawberry Apr 27 '15 at 06:49
Look at the images...the difference was for just 4 rows, it would mean a lot if working for lakhs of rows – Abhinav Apr 27 '15 at 07:18
1

Dont blatantly say It wouldnt, give an example to prove your point – Abhinav Apr 27 '15 at 08:39

MySQL - SUM of rows excluding duplicates

2 Answers2