Make a select with count and add

follows a solution using CASE

SELECT COUNT(*) AS 'quant_total', 
            (case when empresa in ('X','Y') 
                  then 'GRUPO XY' else empresa end) empresa, turma 
FROM 'alunos' 
    WHERE data_matricula >= '2014-01-01' AND data_matricula <= '2014-02-01' 
    AND substr(turma, 1,4) IN ('AAAA') AND situacao = 'AT' 
    GROUP BY (case when empresa in ('X','Y') 
                   then 'GRUPO XY' else empresa end), turma

A better solution would be to create a GROUP column in the COMPANY table, you could use this field to group companies

I do not know if I understood correctly what you need. Maybe the following query will help. I used two SELECT commands, one to calculate the sum of students per company and class and another to calculate the total for companies 'D' and 'F' .

SELECT empresa, turma, count(*) AS 'quant_total'
FROM alunos
GROUP BY empresa, turma
UNION
SELECT empresa, 'Total' AS turma, count(*) AS 'quant_total'
FROM alunos
WHERE empresa IN ('D', 'F')
GROUP BY empresa;

The result is

+---------+-------+-------------+
| empresa | turma | quant_total |
+---------+-------+-------------+
| D       | AAA   |           3 |
| D       | BBB   |           3 |
| D       | CCC   |           4 |
| F       | AAA   |           3 |
| F       | BBB   |           1 |
| F       | CCC   |           2 |
| X       | AAA   |           3 |
| X       | BBB   |           3 |
| X       | CCC   |           2 |
| X       | DDD   |           1 |
| Y       | AAA   |           2 |
| Y       | BBB   |           2 |
| Y       | CCC   |           3 |
| D       | Total |          10 |
| F       | Total |           6 |
+---------+-------+-------------+

Since the alunos table I used to test is

+----+-----------+---------+-------+
| id | nome      | empresa | turma |
+----+-----------+---------+-------+
|  1 | Helena    | X       | AAA   |
|  2 | Davi      | X       | BBB   |
|  3 | Gabriela  | X       | CCC   |
|  4 | Laura     | X       | BBB   |
|  5 | Beatriz   | X       | AAA   |
|  6 | Enzo      | X       | CCC   |
|  7 | Valentina | X       | DDD   |
|  8 | Samuel    | X       | BBB   |
|  9 | Rafael    | X       | AAA   |
| 10 | Lucas     | Y       | CCC   |
| 11 | Gabriel   | Y       | AAA   |
| 12 | Maria     | Y       | BBB   |
| 13 | Ana       | Y       | BBB   |
| 14 | Guilherme | Y       | CCC   |
| 15 | Luiza     | Y       | AAA   |
| 16 | Heitor    | Y       | CCC   |
| 17 | Arthur    | D       | BBB   |
| 18 | Alice     | D       | BBB   |
| 19 | Bernardo  | D       | CCC   |
| 20 | Manuela   | D       | AAA   |
| 21 | Mariana   | D       | BBB   |
| 22 | Gustavo   | D       | CCC   |
| 23 | Isadora   | D       | CCC   |
| 24 | Felipe    | D       | AAA   |
| 25 | Julia     | D       | CCC   |
| 26 | Matheus   | D       | AAA   |
| 27 | Rafaela   | F       | BBB   |
| 28 | Isabella  | F       | CCC   |
| 29 | Miguel    | F       | AAA   |
| 30 | Giovanna  | F       | CCC   |
| 31 | Nicolas   | F       | AAA   |
| 32 | Pedro     | F       | AAA   |
+----+-----------+---------+-------+

Please, better detail your question if the answers given here do not solve your problem.

Hi friends, I'm redoing my asking here, so that you understand better. Motta's response is almost as I want, but only added the companies ('X', 'Y'), the rest of the result that I hope, mentioned below does not trouce, but anyway thank you very much for the force.

The question is, in the school there are several classes with students from different companies, given a certain period and selection of one or more classes, I need to make a SELECT to bring total amount of students enrolled in the school, grouping by class and company . Logic is, if they are business students - > ('X' and 'Y'), companies - > ('D' and 'F') add up the number of students of these two companies, to obtain a unique result, if it is students of company 'P' to bring total amount of students only of this company, and finally, if they are students of any another company, to add to the total student numbers of these companies, to obtain a single result, all grouped by class and company.

At the moment, you can only do a SELECT that brings the total number of students grouped by class and company. But I do not know how to calculate the sum of the total number of students in the companies ('X' and 'Y'), companies - > ('D' and 'F'), to get a unique result of them.

Get the Solution guys, Thanks everyone for the help.

Solution:

SELECT COUNT (*) AS 'quant_total', (CASE WHEN company IN ('X', 'Y') THEN 'X / Y' WHEN company IN ('D', 'F') THEN 'D / F' WHEN company = 'P' THEN 'EMP.P' ELSE 'OR' END) company, class FROM alunos WHERE data_mat> = '$ dt_inicio' AND data_mat