select count Max（value）with where [closed]

Hello you see in picture attached the table that I want to extract data using this query

SELECT count(idmilestone) 
FROM  milestoneevent 
WHERE max(idmilestone=1)

I want to count All the files with last milestone=1 in my case it must return 5 because two files their milestone now is 2 please help me to achieve that

select count('id')
from (select idfile, max(idmilestone) as max_idmilestone
      from milestoneevent
      group by idfile
     ) d
where max_idmilestone = 1;

Try This

SELECT count(idmilestone) 
FROM  milestoneevent 
WHERE idfile NOT IN(SELECT idfile 
    FROM milestoneevent 
    WHERE idfile > 1)

For the data that you have provided, the milestones are increasing numbers. If this is generally true, you can simply do:

select count(*)
from (select idfile, max(idmilestone) as max_idmilestone
      from milestoneevent
      group by idfile
     ) m
where max_idmilestone = 1;

Alternatively, you can use the dat to define the last one:

select count(*)
from milestonemevent me
where me.dat = (select max(me2.dat)
                from milestonemevent me2
                where me.idfile = me2.idfile
               ) and
      me.idmilestone = 1;

please try this, it will give the count of idmilestone which is the least one i.e 1.

SELECT count(idmilestone) FROM  milestoneevent 
WHERE idmilestone =(SELECT MIN(idmilestone) FROM milestoneevent)

if you want count which is equal to 1, then try this

SELECT count(idmilestone) FROM  milestoneevent 
WHERE idmilestone =1

You can use a LEFT JOIN for this:

SELECT t1.id, t1.idfile, t1.idmilestone
FROM milestoneevent AS t1
LEFT JOIN milestoneevent AS t2 
   ON t1.idfile = t2.idfile AND  t1.dat < t2.dat  
WHERE t1.idmilestone = 1 AND t2.id IS NULL

Demo here

To count use COUNT:

SELECT COUNT(*)
FROM milestoneevent AS t1
LEFT JOIN milestoneevent AS t2 
   ON t1.idfile = t2.idfile AND  t1.dat < t2.dat  
WHERE t1.idmilestone = 1 AND t2.id IS NULL
GROUP BY t1.idfile 

Try this:

SELECT COUNT(*) 
FROM (
    SELECT idmilestone
    FROM milestoneevent 
    GROUP BY idmilestone
    HAVING COUNT(*) = 1
)
WHERE idmilestone=1