SQL MIN() with COUNT()

MIN() with COUNT()

In this page, we are discussing the usage of SQL COUNT() function along with the SQL MIN() in a query to get a complex result.

Example:

Sample table: orders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
.........
    200102       2000            300 25-MAY-08 C00012          A012

View the table

To get minimum number of agents mentioned as 'mycount' which comes from a group of result set from a query [SELECT agent_code,COUNT(agent_code) mycount...] with the following condition -

 to produce data 'agent_code' and the number of agents as the column alias
 'mycount' into the result set from the 'orders' table with the following
condition -

'agent_code' should comes in a group,

the following SQL statement can be used :

SELECT MIN(mycount) -- 1. Selecting the minimum value of 'mycount'
FROM ( -- 2. Subquery: Creating a derived table
    SELECT agent_code, COUNT(agent_code) mycount -- 3. Selecting 'agent_code' and its count, aliased as 'mycount'
    FROM orders -- 4. From the 'orders' table
    GROUP BY agent_code -- 5. Grouping the results by 'agent_code'
);

Explanation:

• The query starts by selecting the minimum value of mycount.
• It includes a subquery where a derived table is created. This means that the subquery generates a temporary table from the result set of the inner query.
• Within the subquery, it selects two columns: agent_code and the count of occurrences of agent_code, aliased as mycount. This count represents the number of orders associated with each agent_code.
• The results are then grouped by agent_code. This means that it groups the orders based on the agent_code.
• The outer query then selects the minimum value from the mycount column generated by the subquery.
• The result of this query will be the minimum count of orders associated with any agent_code.

Output:

MIN(MYCOUNT)
------------
           1

All Aggregate Functions

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

PREV : Min group by, order by
NEXT :SQL Arithmetic function