CODEWITHSUNDEEP
sqloracle-databaseaggregate-functionsoracle-sqldeveloper
neo
neo

Reputation: 2471

SQL - combination of MIN and COUNT

This is in Oracle database. Say I have the following table A:

column1 column2
id1       a
id2       a 
id3       a
id4       b
id5       b
id6       c

So what I want the sql does is: First count there's three As and two bs and one c, then based on the counts return me the smallest number of these counts, in this case is 1 (because we only have one c)

Can this be achieved somehow by using the combination of MIN and COUNT?

Upvotes: 3

Views: 2100

Answers (5)

Mureinik
Mureinik

Reputation: 311188

SELECT MIN(cnt)
FROM   (SELECT   COUNT(*) AS cnt
        FROM     my_table
        GROUP BY column2)

EDIT:
As ElmoVanKielmo noted, it's somewhat pointless to offer a solution without explaining it.
The inner query groups the data by column2 values, and return the number of rows for each one. The outer query treats these as just a bunch of numbers, and returns the minimal value among them.

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18737

Try this:

SELECT MIN(Count) as MinVal
FROM
(SELECT column2,COUNT(column2) as Count
FROM TableA
GROUP BY column2) T

Explanation:

Inner query select the counts of column2 for each value of column2 in the table. Then with the outer query, the minimum count is selected.

Upvotes: 0

MDiesel
MDiesel

Reputation: 2667

This should work for you

SELECT *
FROM(
SELECT Column2, COUNT(Column1)
FROM TableA
GROUP BY Column2
ORDER BY COUNT(Column1))
WHERE Rownum = 1

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

In Oracle you can do this directly; count per group and use MIN on the results to get back one row with the desired value.

select min(count(*))
from tablea
group by column1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you are using Oracle 12, you can do this without a subquery:

select count(*) as cnt
from table t
group by cnt
order by cnt asc
fetch first 1 row only;

For those familiar with MySQL or Postgres, fetch first 1 row only is equivalent to limit, and allows you to limit the number of output rows without using a subquery.

Upvotes: 0

Related Questions