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
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
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