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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading