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:

   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
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

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.

Previous: Min group by, order by
Next: SQL Arithmetic function