使用mysql和count请求时遇到麻烦

I met some trouble while using mysql, In fact I have a table formated like that:

n_doss   etat     date       etc...
-----------------------------------
145        N      20/10
145        EC     15/10
146        N      25/10        

What I would like to do is for each etat to count how much folder I have (folders are identified by n_doss).

the trouble is that the last etat that is important is the most recent for each file.

So I have to count for each etat how much file I have, but I can not count each etat for each n_doss but just the most recent.

I've tried almost everything but I can not succeed in this query.

I've tried:

SELECT  `etat` , `n_doss`, COUNT(  'etat' ) 
FROM  `commentaire` WHERE  
GROUP BY  `etat` 

This does not work with what I try to do.

I also tried

SELECT `etat` , `n_doss`, COUNT(  'etat' ) 
FROM  `commentaire`   
GROUP BY  `n_doss`
HAVING max(date) with 

with acualy no success. Any kind of help will be much appreciated.

SELECT  a.*, c.totalCOunt
FROM    commentaire a
        INNER JOIN
        (
            SELECT etat, MAX(date) maxDate
            FROM commentaire
            GROUP BY etat
        ) b ON a.etat = b.etat AND
                a.date = b.maxDate
        INNER JOIN
        (
            SELECT etat, COUNT(*) totalCOunt
            FROM commentaire
            GROUP BY etat
        ) c ON a.etat = c.etat

• SQLFiddle Demo

SELECT  c.*, cnt
FROM    (
        SELECT  etat, COUNT(*) AS cnt
        FROM    commentaire
        GROUP BY
                etat
        ) cd
JOIN    commentaire c
ON      c.id =
        (
        SELECT  id
        FROM    commentaire ci
        WHERE   ci.etat = cd.etat
        ORDER BY
                date DESC, id DESC
        LIMIT 1
        )

Create an index on (etat, date, id) for this to work fast.

Try this::

SELECT  `etat` , COUNT(  'n_doss' ) 
FROM  `commentaire`  
GROUP BY  `etat`