0

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.

APC
  • 144,005
  • 19
  • 170
  • 281
tomss
  • 269
  • 4
  • 6
  • 12

4 Answers4

1

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • This finds the minimum odd code, he wants the odd code with the minimum count. – Barmar Nov 24 '12 at 18:04
  • This queries the WORKERS table twice, which could be quite expensive in real life. @bonsvr 's solution with the WITH clause is more efficient. – APC Nov 25 '12 at 10:53
  • Oh, and the revised version uses `LIMIT`, which is not Oracle syntax (although to be fair the OP didn't originally specify the database product). – APC Nov 25 '12 at 10:54
1

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)
APC
  • 144,005
  • 19
  • 170
  • 281
bonsvr
  • 2,262
  • 5
  • 22
  • 33
1

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

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
0
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

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I have one problem my database seems that doesn't accept a SELECT inside a FROM – tomss Nov 24 '12 at 18:51
  • What database are you using? You should tag the question with it. – Barmar Nov 24 '12 at 18:56
  • Oracle SQL Developer. Sorry. – tomss Nov 24 '12 at 19:04
  • I don't use Oracle, but from googling the `ORA-00933` error code it looks like it doesn't like `ORDER BY` in the subquery. I'm trying to modify it, but can't get even a simple query to work at sqlfiddle when I select Oracle. Maybe some Oracle wiz can figure out the revision. – Barmar Nov 24 '12 at 19:35