Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL query to fetch the maximum cumulative value
For this, use aggregate function COUNT(*) along with subquery. GROUP BY is also used.
Let us create a table −
mysql> create table demo23 −> ( −> id int not null auto_increment primary key, −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.65 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo23(value1,value2) values(5,600); Query OK, 1 row affected (0.20 sec) mysql> insert into demo23(value1,value2) values(20,800); Query OK, 1 row affected (0.06 sec) mysql> insert into demo23(value1,value2) values(7,400); Query OK, 1 row affected (0.20 sec) mysql> insert into demo23(value1,value2) values(6,500); Query OK, 1 row affected (0.17 sec) mysql> insert into demo23(value1,value2) values(10,300); Query OK, 1 row affected (0.12 sec) mysql> insert into demo23(value1,value2) values(11,500); Query OK, 1 row affected (0.14 sec)
Display records from the table using select statement −
mysql> select *from demo23;
This will produce the following output −
+----+--------+--------+ | id | value1 | value2 | +----+--------+--------+ | 1 | 5 | 600 | | 2 | 20 | 800 | | 3 | 7 | 400 | | 4 | 6 | 500 | | 5 | 10 | 300 | | 6 | 11 | 500 | +----+--------+--------+ 6 rows in set (0.00 sec)
Following is the query for maximum cumulative −
mysql> select total_value, count(*) as number_of_occurrences −> from ( −> select value1*value2 as total_value −> from demo23 −> ) t −> group by total_value −> order by total_value desc −> limit 1 ;
This will produce the following output −
+-------------+-----------------------+ | total_value | number_of_occurrences | +-------------+-----------------------+ | 16000 | 1 | +-------------+-----------------------+ 1 row in set (0.00 sec)
Updated on: 19-Nov-2020
173 Views
Advertisements