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

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

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

+------+-------+| id   | Total |
+------+-------+|    1 |  4000 |
|    2 |  4000 |+------+-------+
2 rows in set (0.00 sec)

AmitDiwan

Published on 11-Dec-2020 05:44:15

Previous Page Print Page