Q
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'
group 
    by fid
having count(*) > 2

The HAVING clause is evaluated after the groups are formed.

This was last published in September 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close