Problem solve Get help with specific problems with your technologies, process and projects.

DISTINCT is not a function

My question is related to count(*) and distinct. How can I list results where count(*) is greater than 2?

My question is related to count(*) and distinct. For example,

select distinct(fid), count(*)
from formstatus
where to_char(eventdatetime,'mm/yyyy')='01/2005'
and doctype='ZB01'
and lifestatus='AP'
group by fid 

The result is:

 fid  count(*)
12345  1
12346  2 
12347  1 
12348  1 
12349  3 
12350  4
12351  1 
12352  2

How can I list results where count(*) is greater than 2?

There must be some book or course or web site out there which (erroneously!) shows DISTINCT being used as a function. If anyone knows where, please contact me and let me know. DISTINCT is not a function. DISTINCT does not take arguments, except insofar as the entire SELECT list of columns is its argument.

In any case, you don't need DISTINCT here. Since you're using GROUP BY, DISTINCT is redundant. (This isn't always true, because there are situations where both DISTINCT and GROUP BY are required, but these situations are rare, and this isn't one of them.) The groups formed by GROUP BY are distinct by definition, so in this case every fid in your result will be distinct.

To obtain only those results where the count is greater than 2, use HAVING.

select fid
     , count(*)
  from formstatus
 where to_char(eventdatetime,'mm/yyyy') 
         = '01/2005'
   and doctype = 'ZB01'
   and lifestatus = 'AP'
    by fid
having count(*) > 2

The HAVING clause is evaluated after the groups are formed.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.