Return quantity of SQL grouped items

Question

I have the following SQL

SELECT `setor`.`set_setor` as age_status_texto, `prospect_agendamento`.`age_status`
FROM (`prospect_agendamento`)
JOIN `setor` ON `setor`.`set_cod`=`prospect_agendamento`.`age_status`
INNER JOIN `prospect` ON `prospect`.`pro_id`=`prospect_agendamento`.`age_id_prospect`
JOIN `unidade` ON `unidade`.`uni_id`=`prospect`.`uni_id`
JOIN `curso` ON `curso`.`cur_id`=`prospect`.`cur_id`
JOIN `usuario` ON `usuario`.`usu_id`=`prospect_agendamento`.`age_cod_consultora_agendado`
WHERE `age_data_agendado_para` BETWEEN concat('2017-04-01', ' 00:00:00') AND concat('2017-11-21', ' 23:59:59')
GROUP BY `prospect_agendamento`.`age_status`
ORDER BY `prospect_agendamento`.`age_data_agendado_para` DESC

This is the result of SQL:

Array
(
    [0] => stdClass Object
        (
            [age_status_texto] => 2 - Compareceu / matriculado
            [age_status] => 46
        )

    [1] => stdClass Object
        (
            [age_status_texto] => 3 - Compareceu / proposta
            [age_status] => 48
        )

    [2] => stdClass Object
        (
            [age_status_texto] => 5 - Não compareceu
            [age_status] => 53
        )

    [3] => stdClass Object
        (
            [age_status_texto] => 1 - No aguardo
            [age_status] => 50
        )

)

My idea is to group by age_status, and I need to know how many records of each status I have in the table. How do I do? Once I’ve gathered the result.

Answer 1

To count the records it is necessary to use the aggregation function count().

Look at this query:

SELECT `setor`.`set_setor` as age_status_texto,
`prospect_agendamento`.`age_status`,
count(`prospect_agendamento`.`age_status`) as quantidade_registros
FROM (`prospect_agendamento`)
JOIN `setor` ON `setor`.`set_cod`=`prospect_agendamento`.`age_status`
INNER JOIN `prospect` ON `prospect`.`pro_id`=`prospect_agendamento`.`age_id_prospect`
JOIN `unidade` ON `unidade`.`uni_id`=`prospect`.`uni_id`
JOIN `curso` ON `curso`.`cur_id`=`prospect`.`cur_id`
JOIN `usuario` ON `usuario`.`usu_id`=`prospect_agendamento`.`age_cod_consultora_agendado`
WHERE `age_data_agendado_para` BETWEEN concat('2017-04-01', ' 00:00:00') AND concat('2017-11-21', ' 23:59:59')
GROUP BY `prospect_agendamento`.`age_status`, `setor`.`set_setor`
ORDER BY `prospect_agendamento`.`age_data_agendado_para` DESC