CODEWITHSUNDEEP
mysql
Sumit Bijvani
Sumit Bijvani

Reputation: 8179

MySQL - SUM of rows excluding duplicates

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.

Upvotes: 0

Views: 1034

Answers (2)

Abhinav
Abhinav

Reputation: 8168

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 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 )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269723

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

Upvotes: 2

Related Questions