DISTINCT is not a function

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle and SQL Oracle tutorial library: SearchOracle.com's learning guides Can I specify Oracle column order in my database table? Review: Oracle's 11g R2 database has some good and bad SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL datatypes in Oracle Stored procedures in PL/SQL PL/SQL functions and triggers in Oracle Do I need a license for SQL Developer Data Modeler in Oracle? Using the SQL GROUP BY clause for counting combinations Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.