How to add up the amount of records where the penultimate record enters Where?

You can get the desired result by calculating the JOÃO position in the child table.

SELECT COUNT(1)
  FROM chamado chmd
       INNER JOIN (SELECT @row_number := CASE
                                           WHEN @num_chamado = comp.num_chamado THEN @row_number + 1
                                           ELSE 1
                                         END AS row_number,
                          @num_chamado := comp.num_chamado as num_chamado,
                          comp.id_complemento,
                          comp.nome
                     FROM complemento comp
                    ORDER BY comp.num_chamado, comp.id_complemento DESC) x ON x.num_chamado = chmd.num_chamado
 WHERE x.nome LIKE 'JOÃO%'
   AND x.row_number = 2

• A JOIN is made with a subquery that should return the complement records with their respective position;

• We use the variable @row_number that will be restarted (with a value of 1) whenever the number of the current call ( comp.num_chamado ) is different from the previous call @num_chamado . Otherwise the variable will be incremented;

• We assign the call number of the current line to the variable;

• The ORDER BY is first performed by the called number, ensuring that there is no return to the same code if you have already moved to the next one. Next, the ordering is done by the code of the decreasing complement ( DESC ) guaranteeing that the last code will get row_number 1 and penultimate 2;

• With the result we filter with LIKE the names that start with JOÃO and the records that have row_number equal to 2, that is, they are the second record considering id_complemento back to front;

• We use COUNT to check the amount of records that meet these conditions.

See working in SQL Fiddle .

Reference: MySQL row_number, This Is How You Emulate It

Without the structure of the tables it is a bit complicated, but try:

SELECT 
COUNT(distinct chmd.num_chamado) 
FROM chamado AS chmd
INNER JOIN complemento AS comp ON comp.num_chamado = chmd.num_chamado 
                               AND comp.id_complemento = 
                                 (select 
                                      max(x.id_complemento) 
                                  from complemento x 
                                  where x.num_chamado = chmd.num_chamado 
                                  and x.id_complemento < 
                                     (select 
                                          max(y.id_complemento) 
                                      from complemento y 
                                      where y.num_chamado = x.num_chamado))
WHERE comp.nome like 'joao%'