Unable to get the right output from Oracle SQL

I have a table with field1, field2, field3, … and I need to count the number of items in field1 such that I return all recor开发者_高级运维ds(field1,filed2,field3,…) that occur 6 times or less in the table.

My SQL code is:

SELECT field1, field2, field3, count(field1) CNT
FROM myTable
WHERE trunc(date)  = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
GROUP BY field1
HAVING COUNT(field1)  <  7;

The output that I am getting from the above code is all records are returned from the table not what I expected? Any help would be appreciated!!

I think you need to use a subquery:

SELECT field1, field2, field3, 
FROM myTable
WHERE trunc(date)  = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
AND field1 in 
   (SELECT field1
    FROM mytable
    GROUP BY field1
    HAVING COUNT(field1)  <  7); 

WITH tmp AS
(
    SELECT field1, COUNT(1) as CountOfField1
    FROM myTable
    WHERE trunc(date) = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
    GROUP BY field1
    HAVING COUNT(field1) < 7
)
SELECT  mytable.field1, mytable.field2, mytable.field3, tmp.CountOfField1
FROM    myTable 
        INNER JOIN tmp 
            ON myTable.Field1 = tmp.Field1

Yet another way to do it:

SELECT t.field1, t.field2, t.field3, 
FROM myTable t
WHERE trunc(t.date) = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
  AND EXISTS
  ( SELECT *
    FROM mytable t2
    WHERE t2.field1 = t.field1
      AND trunc(t2.date) = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
    GROUP BY t2.field1
    HAVING COUNT(t2.field1)  <  7
  )
;