Select with Count and add

Asked

Viewed 94 times

1

I need to perform a select, which bring the total number of students enrolled per class and company, only when it is company ('X' e 'Y') add up the total amount of them, ('D' e 'F') also add up the total amount of them, for company. 'P' bring total amount of students only from this company and the other companies bring the total sums of them.

I perform this select, through the period and the class, which can be one or more classes. However I do not know how to bring the total sum of companies ('X' e 'Y') add up the total amount of them, ('D' e 'F').

SELECT COUNT(*) AS 'quant_total', 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 empresa, turma

Upshot:

quant_total       |empresa       |turma     

1                 | X            | AAA
4                 | X            | BBB
4                 | X            | CCC
3                 | X            | DDD
1                 | F            | AAA
16                | D            | AAA
33                | D            | HHH
28                | D            | XXX
18                | D            | DDD
12                | P            | AAA
1                 | P            | HHH
11                | P            | BBB
14                | P            | CCC
23                | P            | ZZZ
1                 | P            | KKK
3                 | Y            | AAA
5                 | Y            | BBB
4                 | Y            | CCC
5                 | Y            | DDD
1                 | O            | BBB
php sql
  • 2

    When making a question, never abbreviate the words, here the understanding is paramount.

  • I don’t know much about SELECT, so a path in my view would be in PHP WHILE to perform the class validations, but I still can’t imagine how if something comes up I give you a touch.

3 answers

1

Hi friends I’m redoing my ask here, so you understand better. Motta’s reply, is almost as I want, but only added the companies ('X','Y'), the rest of the result I hope, mentioned below did not trouce, but even so thank you very much for the strength.

The point is, at school there are several classes with students from different companies, given a certain period and selection of one or more classes, I need to perform a SELECT to bring total number of students registered at the school, grouping by class and company. Logic is, if you are students of companies -> ('X' and 'Y'), companies -> (’D' and 'F') add up the amount of students of these two companies, to obtain a unique result, if you are students of company 'P' bring total amount of students only from this company, and finally, if you are students of any other company, add to the total student quantities of these companies, to obtain also a single result, all this grouped by class and company.

At the moment only manage to make a SELECT that brings the total amount of students grouped by class and company. However, I do not know how to perform the sum of the total number of students of companies ('X' and 'Y'), companies -> (’D' and 'F'), to obtain a single result of them.

Get the Solution guys, Thank you all for your 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 <= '$dt_fim' AND substr(class, 1,4) IN ($class) AND situacao = 'AT' GROUP BY (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), class

1

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 , could be to use this field to group companies

1

I’m not sure I understand what you need. Maybe the following query will help. I used two commands SELECT, one to calculate the sum of pupils per company and class and one to calculate the total for enterprises '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 |
+---------+-------+-------------+

Being that the table alunos that 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 detail your question better if the answers presented here do not solve your problem.

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.