MySQL - SUM rows with same ID?

To sum rows with same ID, use the GROUP BY HAVING clause.

Let us create a table −

Example

mysql> create table demo84
   -> (
   -> id int,
   -> price int
   -> )
   -> ;
Query OK, 0 rows affected (0.60

Insert some records into the table with the help of insert command −

Example

mysql> insert into demo84 values(1,2000);
Query OK, 1 row affected (0.08

mysql> insert into demo84 values(1,2000);
Query OK, 1 row affected (0.14

mysql> insert into demo84 values(2,1800);
Query OK, 1 row affected (0.14

mysql> insert into demo84 values(2,2200);
Query OK, 1 row affected (0.14

mysql> insert into demo84 values(3,1700);
Query OK, 1 row affected (0.12

Display records from the table using select statement −

Example

mysql> select *from demo84;

This will produce the following output −

Output

<p>+------+-------+</p>| id   | price |
<p>+------+-------+</p>|    1 |  2000 |
<p>|    1 |  2000 |</p><p>|    2 |  1800 |</p>|    2 |  2200 |
<p>|    3 |  1700 |</p>+------+-------+
<p>5 rows in set (0.00 sec)</p>

Following is the query to sum rows with same id −

Example

mysql> select id,sum(price) as Total from demo84
   -> group by id
   -> having sum(demo84.price) >=2000;

This will produce the following output −

Output

<p>+------+-------+</p>| id   | Total |
<p>+------+-------+</p>|    1 |  4000 |
<p>|    2 |  4000 |</p>+------+-------+
<p>2 rows in set (0.00 sec)</p>