Count() " taken the range from 100 and above

select count(*) from (
 select ROW_NUMBER() over(order by pk)id,* from #leave
  ) a where id > 100


here

#leave    ---- Table Name
pk        ----- Primaary key of the table(you can use whatever you want)
in where condition you can put you condition.
if you want start count from 100 to 199 jut put the condition
like

where id > 100 and id < 200

select count(*) + 100 from (
 select ROW_NUMBER() over(order by pk)id,* from #leave
  ) a where id > 100

try this.:)

select count(*) , trunc(a.Date),d.cita
from dbdev.sub a , dbdev.num b, dbdev.dis c , dbdev.cit d
where a.sen=b.mo
and b.di =c.id
and c.id =d.id
and a.date >=trunc(to_Date('01/01/2011','dd/mm/yyyy'))
and a.date <trunc(sysdate)>

group by trunc(a.Date) ,d.cita
order by trunc(a.Date)


This is my query and the count will show all the numbers from 0 and above , but my point is to show only above 100 not from 0 and above !!!

and BTW thanks alot for your ideas am really appreciate that , but my problem did not solve yet !

Best regards
Ahmed

Hi Ahmed,

If you meant IDs greater than 100 then you should add another condition to your where clauses by using an and keyword (and its very obvious and I'm sure that you are aware of that) :

select count(*) , trunc(a.Date),d.cita
from dbdev.sub a , dbdev.num b, dbdev.dis c , dbdev.cit d
where a.sen=b.mo
and b.di =c.id
and c.id =d.id
and a.date >=trunc(to_Date('01/01/2011','dd/mm/yyyy'))
and a.date <trunc(sysdate)>

and a.id >= 100

group by trunc(a.Date) ,d.cita
order by trunc(a.Date)

But I guess that you want all counts which are greater than 100. in that case you should use HAVING clause :

select count(*) , trunc(a.Date),d.cita
from dbdev.sub a , dbdev.num b, dbdev.dis c , dbdev.cit d
where a.sen=b.mo
and b.di =c.id
and c.id =d.id
and a.date >=trunc(to_Date('01/01/2011','dd/mm/yyyy'))
and a.date <trunc(sysdate)>

group by trunc(a.Date) ,d.cita
having count(*)>=100
order by trunc(a.Date)

Hope it helps.

well ,
it will take the values from 99 and above , but my point is to begin the count from 100 not 0 !!