SQL MAX() with COUNT()
MAX() with Count function
In this part, you will see the usage of SQL COUNT() along with the SQL MAX().
Example:
To get the maximum number of agents as column alias 'mycount' from the 'orders' table with the following condition -
1. 'agent_code' should be in a group,
the following SQL statement can be used :
SELECT MAX (mycount)
FROM (SELECT agent_code,COUNT(agent_code) mycount
FROM orders
GROUP BY agent_code);
Sample table: orders
Output:
MAX(MYCOUNT) ------------ 7
Pictorial Presentation :

SQL MAX() and COUNT() with HAVING
To get data of 'agent_code', and number of agents for each group of 'agent_code' from the orders table with the following conditions -
'agent_code' for a group will be equal to the result of an outer query [SELECT MAX(agent_code).......] with following condition -
the outer query produce the maximum number of agents mentioned as
'mycount' from an inner query [SELECT agent_code,
COUNT(agent_code) mycount FROM orders GROUP BY agent_code]
with following condition -
the inner query produced the data 'agent_code' number of agents as column alias 'mycount' from the 'orders' table with the following condition -
'agent_code' should be in a group,
the following SQL statement can be used :
SELECT agent_code, COUNT(agent_code)
FROM orders GROUP BY agent_code
HAVING COUNT (agent_code)=(
SELECT MAX(mycount)
FROM (
SELECT agent_code, COUNT(agent_code) mycount
FROM orders
GROUP BY agent_code));
Sample table: orders
Output:
AGENT_CODE COUNT(AGENT_CODE) ---------- ----------------- A002 7

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition
Here is a slide presentation of all aggregate functions.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Max Date
Next: Min function
SQL: Tips of the Day
Difference between primary key and unique key:
Primary Key:
- There can only be one primary key constraint in a table
- In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
- Primary Key is a unique key identifier of the record
Unique Key:
- Can be more than one unique key in one table
- Unique key can have NULL values
- It can be a candidate key
- Unique key can be NULL ; multiple rows can have NULL values and therefore may not be considered "unique"
Ref : https://bit.ly/2XNgBd0
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises