CODEWITHSUNDEEP
sqloracle-databaseaggregate-functionsoracle-sqldevelopertop-n
tomss
tomss

Reputation: 269

Use of MIN and COUNT

Can you give me an example at use of the min and count at the same query? For example on this table I want to know the lower number of workers with an odd Code (1 with the code 5).

 Table Workers
      Code
       1
       2
       2
       2
       3
       3
       1
       5

Thanks.

Upvotes: 0

Views: 11831

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Another solution that uses window functions and keeps all tied results. If you don't want many results when there are ties (but only an arbitrary one), use ROW_NUMBER() instead of RANK():

SELECT code
FROM
  ( SELECT code 
         , RANK() OVER (ORDER BY COUNT(*)) AS rnk
    FROM workers
    WHERE MOD(code, 2) = 1
    GROUP BY code 
  ) tmp
WHERE rnk = 1 ;

Tested in (Oracle 11g2): SQL-Fiddle

Upvotes: 1

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

This is for ORACLE and solves your problem. 

with newt as
  (  select code, count(*) cnt
     from workers
     where mod(code, 2) = 1
     group by code)
select * 
from newt
where cnt in (select min(cnt) 
              from newt)

Upvotes: 1

Barmar
Barmar

Reputation: 780879

select Code, count(*) MinCount
from Workers
where mod(code, 2) = 1
group by Code
order by MinCount
limit 1

SqlFiddle

Note that if there are multiple codes with the minimum count, this selects just one of them arbitrarily. If you want all of them, that complicates things and you'll need a join with a subquery. Here's that query:

SELECT w.Code, CodeCount
FROM (SELECT Code, count(*) CodeCount
      FROM Workers
      WHERE mod(code, 2) = 1
      GROUP BY Code) w
JOIN (SELECT Code, count(*) MinCount
      FROM Workers
      WHERE mod(code, 2) = 1
      GROUP BY Code
      ORDER BY MinCount
      LIMIT 1) MinQuery
ON CodeCount = MinCount

SqlFiddle

Upvotes: 0

Bohemian
Bohemian

Reputation: 424993

You would need a subquery to find the minimum, then use that to query again for the count:

select code, count(*)       -- get the count for the code found in the subquery
from workers
where code = (
    select min(code)        -- return the minimum code found
    from workers
    where mod(code, 2) = 1) -- only odd codes
group by code;              -- group by the non-aggregated column(s0

Edited:

From comments, it seem you want the odd code with the least workers:

select code, count(*)
from workers
where mod(code, 2) = 1
group by code
order by 2
limit 1;

You don't say which database you're using, so the syntax for "returning only the first row" may vary from "LIMIT 1", which is the mysql way of doing it.

Upvotes: 1

Related Questions