help with oracle sql case statement using count criteria

So far, this query works using exists:

SELECT CASE WHEN EXISTS
                   ( SELECT *
                     FROM TEST1
                     WHERE times开发者_开发技巧tamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
                   )
            THEN 0
            ELSE 1
       END AS FLG
FROM dual

Now, I need to add another criteria. Since my data has these consistent count values of 100 for example:

SQL> select count(*), timestamp from TEST1 group by timestamp order by 2 desc;

  COUNT(*)  TIMESTAMP
---------- ----------
      100 2.0111E+11
      100 2.0111E+11

I now need to alter this query so that I get only those that have a count(*) of 100 and if they do then set my flag = 0 and if not set my flag =1.

I'm not sure where to add this count criteria value of 100. Can I still use CASE for this?

You could try looking into the having-statement:

select count(*), timestamp
from TEST1
group by timestamp
having count(*) = 100

Basically it's the same as where, but only for aggregates.

Something like this

SELECT CASE WHEN EXISTS( SELECT *
                 FROM TEST1
                 WHERE timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
               ) 
            and 
               DECODE(( SELECT count(*)
                 FROM TEST1
                 WHERE timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
               ),100,true,false)
        THEN 0
        ELSE 1
   END AS FLG
FROM dual