MySQL - SUM rows with same ID?

MySQLMySQLi Database

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)

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