How to display records having sum between a specific range using GROUP BY, HAVING and ORDER BY in a single MySQL query?

Let us first create a table −

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   CustomerName varchar(20),
   ProductPrice int
);
Query OK, 0 rows affected (0.70 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(CustomerName,ProductPrice) values('Chris',600);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(CustomerName,ProductPrice) values('David',450);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable(CustomerName,ProductPrice) values('Chris',980);
Query OK, 1 row affected (0.40 sec)
mysql> insert into DemoTable(CustomerName,ProductPrice) values('Mike',1200);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(CustomerName,ProductPrice) values('Chris',400);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(CustomerName,ProductPrice) values('David',1200);
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+--------------+--------------+
| Id | CustomerName | ProductPrice |
+----+--------------+--------------+
|  1 | Chris        |          600 |
|  2 | David        |          450 |
|  3 | Chris        |          980 |
|  4 | Mike         |         1200 |
|  5 | Chris        |          400 |
|  6 | David        |         1200 |
+----+--------------+--------------+
6 rows in set (0.00 sec)

Following is the query to display a specific range of numbers −

mysql> select CustomerName,sum(ProductPrice) AS Total_Price
   from DemoTable
   group by CustomerName
   having sum(ProductPrice) between 1600 and 2000
   order by CustomerName DESC;

This will produce the following output −

+--------------+-------------+
| CustomerName | Total_Price |
+--------------+-------------+
| David        |        1650 |
| Chris        |        1980 |
+--------------+-------------+
2 rows in set (0.00 sec)

AmitDiwan

Published on 04-Oct-2019 07:48:08

• Related Questions & Answers
• HAVING with GROUP BY in MySQL
• How to ORDER BY FIELD with GROUP BY in a single MySQL query?
• Using GROUP BY and COUNT in a single MySQL query to group duplicate records and display corresponding max value
• Display the record with non-duplicate Id using MySQL GROUP BY and HAVING
• SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING
• Order by a single field and display rest of the records in the same order with MySQL
• Does MySQL eliminate common subexpressions between SELECT and HAVING/GROUP BY clause? How to test it?
• Difference Between Group By and Order By in SQL
• MySQL Order by a specific column x and display remaining values in ascending order
• A single MongoDB query to orderby date and group by user
• MongoDB query to group records and display a specific value with dot notation
• How to Order by a specific string in MySQL?
• MongoDB Query to select records having a given key?
• ORDER BY a specific word in MySQL
• Order by a function of two columns in a single MySQL query