SELECT
	s1.user,
    s1.date_inscription,
   
    (	SELECT
    		COUNT( * )
    	FROM
        	jos_chronoforms_si s2
        
        WHERE
        	s2.date_inscription = s1.date_inscription AND
            s2.user = s1.user AND
            s2.action = "Inscription"
    ) as InscriptionCount,
    
    (	SELECT
    		COUNT( * )
    	FROM
        	jos_chronoforms_si s2
        
        WHERE
        	s2.date_inscription = s1.date_inscription AND
            s2.user = s1.user AND
            s2.action = "Confirmation"
    ) as ConfirmationCount

FROM
	jos_chronoforms_si s1

GROUP BY
	s1.user,
    s1.date_inscription