Complex SQL with Mysql

Asked

Viewed 479 times

1

I have these tables and need to do an sql command to receive this answer. I use Mysql.

tabela_estudantes_identificacao tabela_turmas_has_estudantes tabela_sexo_identificacao

tabela_situacao_final

tabela_turmas_identificacao resultado_esperado

I tried it like this and returns zero in all codes:

    SELECT
    COUNT( ei.sexo_id ) AS total_sexo_masculino, 
        the.situacao_final_id 
    FROM 
        turmas_has_estudantes the 
    INNER JOIN
        estudantes_identificacao ei
    ON
       ( the.estudantes_identificacao_id = ei.id 
         AND the.situacao_final_id != 3 
         AND the.situacao_final_id != 5 
         AND the.situacao_final_id != 6 )
    WHERE
         ei.sexo_id = 2
    AND
        the.turmas_id = 492



   SELECT
   COUNT( ei.sexo_id ) AS total_sexo_masculino, 
        the.situacao_final_id 
   FROM 
        turmas_has_estudantes the 
   INNER JOIN
        estudantes_identificacao ei
   ON
        the.estudantes_identificacao_id = ei.id
   WHERE
        ei.sexo_id = 2
   AND
        the.turmas_id = 492
   AND 
        the.situacao_final_id != 3 
   AND 
        the.situacao_final_id != 5 
   AND 
        the.situacao_final_id != 6

If I do so, returns 1:

   SELECT
        COUNT( ei.sexo_id ) AS total_sexo_masculino, 
        the.situacao_final_id 
    FROM 
         turmas_has_estudantes the 
    INNER JOIN
         estudantes_identificacao ei
    ON
         ( the.estudantes_identificacao_id = ei.id 
           AND the.situacao_final_id = 6 )
    WHERE
         ei.sexo_id = 2
    AND
         the.turmas_id = 492
mysql

  • you won’t be able to do this with a single select simple. you need to compute different things that need a group by or where and are still on different tables, so you will need multiple selects to bring the expected result

  • In your "Expected Result" would be the result of several queries, and not just one, are you sure that’s what you want? The most correct would be to make separate consultations, of sex, situation and total.

  • I believe that this consultation requires unions, so it is inherently different consultations

  • I need to return the result of all classes. Class ID = 492 is just a reference

  • @Luissouza made a practical example that will serve any situation that arises

  • There was an error in the table photo class_identification. Corrected

Show 1 more comment

3 answers

3

I made generic SQL:

select
  'Total Estudantes' as Resultado, count(*)  as Total
from 
  ESTUDANTES_IDENTIFICACAO
union
select 
  CONCAT('Sexo ', b.sexo), count(*) 
from 
  ESTUDANTES_IDENTIFICACAO a
  inner join SEXO_IDENTIFICACAO b on b.id = a.sexo_id
group by
  b.sexo
union
select 
  CONCAT('Situacao Final ', IFNULL(b.situacao_final_id, "ND")), count(*) 
from 
  TURMAS_HAS_ESTUDANTES a
  left join SITUACAO_FINAL b on b.id = a.situacao_final
group by
  b.situacao_final_id

Practical example

http://sqlfiddle.com/#! 9/04353fe/10

Final Result

|                     Resultado | Total |
|-------------------------------|-------|
|              Total Estudantes |    15 |
|                 Sexo Feminino |     6 |
|                Sexo Masculino |     9 |
|             Situacao Final ND |    10 |
| Situacao Final Reclassificado |     1 |
|     Situacao Final Remanejado |     2 |
|    Situacao Final Transferido |     2 |

Practical Example Filtering Class

http://sqlfiddle.com/#! 9/b625e5/3

Result Filtering Class

|                     Resultado | Total |
|-------------------------------|-------|
|              Total Estudantes |     6 |
|                 Sexo Feminino |     2 |
|                Sexo Masculino |     4 |
|             Situacao Final ND |     2 |
| Situacao Final Reclassificado |     1 |
|     Situacao Final Remanejado |     1 |
|    Situacao Final Transferido |     2 |

Structure data

create table ESTUDANTES_IDENTIFICACAO (
  id int not null,
  nome varchar(50),
  sexo_id int,
  deficiancia varchar(50)
);

insert into ESTUDANTES_IDENTIFICACAO values (1, 'nome_1', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (2, 'nome_2', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (3, 'nome_3', 2, null);
insert into ESTUDANTES_IDENTIFICACAO values (4, 'nome_4', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (5, 'nome_5', 2, null);
insert into ESTUDANTES_IDENTIFICACAO values (6, 'nome_6', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (7, 'nome_7', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (8, 'nome_8', 2, null);
insert into ESTUDANTES_IDENTIFICACAO values (9, 'nome_9', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (10, 'nome_10', 2, null);
insert into ESTUDANTES_IDENTIFICACAO values (11, 'nome_11', 2, null);
insert into ESTUDANTES_IDENTIFICACAO values (12, 'nome_12', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (13, 'nome_13', 1, null);
insert into ESTUDANTES_IDENTIFICACAO values (14, 'nome_14', 2, null);
insert into ESTUDANTES_IDENTIFICACAO values (15, 'nome_15', 1, null);

create table SEXO_IDENTIFICACAO (
  id int not null,
  sexo varchar(50)
);

insert into SEXO_IDENTIFICACAO values (1, 'Masculino');
insert into SEXO_IDENTIFICACAO values (2, 'Feminino');

create table TURMAS_HAS_ESTUDANTES (
  id int not null,
  turma_id int,
  estudante_id int,
  situacao_final int  
);

insert into TURMAS_HAS_ESTUDANTES values (1, 492, 1, null);
insert into TURMAS_HAS_ESTUDANTES values (2, 492, 2, 3);
insert into TURMAS_HAS_ESTUDANTES values (3, 492, 3, null);
insert into TURMAS_HAS_ESTUDANTES values (4, 492, 4, 5);
insert into TURMAS_HAS_ESTUDANTES values (5, 492, 5, 6);
insert into TURMAS_HAS_ESTUDANTES values (6, 492, 6, 5);
insert into TURMAS_HAS_ESTUDANTES values (7, 493, 7, null);
insert into TURMAS_HAS_ESTUDANTES values (8, 493, 8, null);
insert into TURMAS_HAS_ESTUDANTES values (9, 493, 9, 3);
insert into TURMAS_HAS_ESTUDANTES values (10, 493, 10, null);
insert into TURMAS_HAS_ESTUDANTES values (11, 493, 11, null);
insert into TURMAS_HAS_ESTUDANTES values (12, 493, 12, null);
insert into TURMAS_HAS_ESTUDANTES values (13, 494, 13, null);
insert into TURMAS_HAS_ESTUDANTES values (14, 494, 14, null);
insert into TURMAS_HAS_ESTUDANTES values (15, 494, 15, null);

create table SITUACAO_FINAL (
   id int not null,
   situacao_final_id varchar(50)
);

insert into SITUACAO_FINAL values (3, 'Remanejado');
insert into SITUACAO_FINAL values (5, 'Transferido');
insert into SITUACAO_FINAL values (6, 'Reclassificado');

create table TURMAS_IDENTIFICACAO (
  id int not null,
  turma_id int
);

insert into TURMAS_IDENTIFICACAO values (1, 492);
insert into TURMAS_IDENTIFICACAO values (2, 493);
insert into TURMAS_IDENTIFICACAO values (3, 494);

Updated (28/02/2018 21:30)

The author requested the separation of the data by final situation. Practical example placed here: http://sqlfiddle.com/#! 9/b625e5/45/0

I thought of almost every possibility :D

New SQL

-- Total geral
select
  'Total Estudantes' as Resultado, count(*)  as Total
from 
  ESTUDANTES_IDENTIFICACAO a
  inner join TURMAS_HAS_ESTUDANTES b on b.estudante_id = a.id
where
  b.turma_id = 492  
union
-- Busca total por situação final
select
  case 
    when b.situacao_final is null then 'Total Estudantes Sem Situação Final' 
    else 'Total Estudantes Com Situação Final' 
  end as Resultado, count(*)  as Total
from 
  ESTUDANTES_IDENTIFICACAO a
  inner join TURMAS_HAS_ESTUDANTES b on b.estudante_id = a.id
where
  b.turma_id = 492
group by
  case 
    when b.situacao_final is null then 'Total Estudantes Sem Situação Final' 
    else 'Total Estudantes Com Situação Final' 
  end  
union
-- Busca por sexo 
select 
  CONCAT('Sexo ', b.sexo), count(*) 
from 
  ESTUDANTES_IDENTIFICACAO a
  inner join SEXO_IDENTIFICACAO b on b.id = a.sexo_id
  inner join TURMAS_HAS_ESTUDANTES c on c.estudante_id = a.id
where
  c.turma_id = 492  
group by
  b.sexo
union
-- Busca total por situação final e sexo
select
  case 
    when c.situacao_final is null then CONCAT('Sexo ', b.sexo,' sem Situação Final') 
    else CONCAT('Sexo ', b.sexo, ' com Situação Final') 
  end as Resultado, count(*)  as Total
from 
  ESTUDANTES_IDENTIFICACAO a
  inner join SEXO_IDENTIFICACAO b on b.id = a.sexo_id
  inner join TURMAS_HAS_ESTUDANTES c on c.estudante_id = a.id
where
  c.turma_id = 492  
group by
  case 
    when c.situacao_final is null then CONCAT('Sexo ', b.sexo,' sem Situação Final') 
    else CONCAT('Sexo ', b.sexo, ' com Situação Final') 
  end   
union
-- Sexo e descricao da situação final
select 
  CONCAT('Sexo ', b.sexo, ' com situacao ', IFNULL(d.situacao_final_id, "ND")), count(*) 
from 
  ESTUDANTES_IDENTIFICACAO a
  inner join SEXO_IDENTIFICACAO b on b.id = a.sexo_id
  inner join TURMAS_HAS_ESTUDANTES c on c.estudante_id = a.id
  left join SITUACAO_FINAL d on d.id = c.situacao_final
where
  c.turma_id = 492    
group by
   CONCAT('Sexo ', b.sexo, ' com situacao ', IFNULL(d.situacao_final_id, "ND"))    
union
select 
  CONCAT('Situacao Final ', IFNULL(b.situacao_final_id, "ND")), count(*) 
from 
  TURMAS_HAS_ESTUDANTES a
  left join SITUACAO_FINAL b on b.id = a.situacao_final
where
  a.turma_id = 492  
group by
  b.situacao_final_id

  • Cool! But, how do I get the data from a particular class? I can put a WHERE. Ex.: WHERE CLASS ID = 492?

  • @Luissouza this very, you can use where in each of the Sqls. Needs some more different result?

  • I put Where a.id = 492 and keep taking the values from the whole table

  • @Luissouza made example filtering class. Anything gives a cry

  • It is returning the total male and total female sex. I need the total discounting those who have the codes in the final situation (3,5 or 6)

  • @Luissouza is here the final answer http://sqlfiddle.com/#! 9/b625e5/9/0

  • @Luissouza super result here http://sqlfiddle.com/#! 9/b625e5/45/0 see if something :D is missing

Show 2 more comments

3

I believe that’s what you want, that returns the totals for each class, grouped by the class:

SELECT
    TI.TURMA_ID,
    COUNT(THE.ESTUDANTE_ID) AS TOTAL ESTUDANTES,
    SUM(CASE WHEN EI.SEXOID = 1 THEN 1 ELSE 0 END) AS SEXO MASCULINO,
    SUM(CASE WHEN EI.SEXOID = 2 THEN 1 ELSE 0 END) AS SEXO FEMININO,
    SUM(CASE WHEN THE.SITUACAO_FINAL = 3 THEN 1 ELSE 0 END) AS SITUACAO_FINAL_3,
    SUM(CASE WHEN THE.SITUACAO_FINAL = 5 THEN 1 ELSE 0 END) AS SITUACAO_FINAL_5,
    SUM(CASE WHEN THE.SITUACAO_FINAL = 6 THEN 1 ELSE 0 END) AS SITUACAO_FINAL_6    
FROM
    TURMAS_HAS_ESTUDANTES THE
INNER JOIN
    TURMAS_IDENTIFICACAO TI ON TI.ID = THE.TURMA_ID
INNER JOIN
    ESTUDANTES_IDENTIFICACAO EI ON EI.ID = THE.ESTUDANTES_IDENTIFICACAO_ID
INNER JOIN
    SEXO_IDENTIFICACAO SE ON SE.ID = EI.SEXO_ID
GROUP BY 
    TI.TURMA_ID

Let me know if you gave the expected result.

  • 1

    The expected result in the question is displayed in rows and not in columns. Another thing, if you have one more situation in the table TURMAS_HAS_ESTUDANTES SQL will not show the new situation

  • I thought it was cool the logic you used in SQL. + 1

  • @Paulo R. F. Amorim I didn’t understand the line: SEXO_IDENTIFICACAO SE ON SE.ID = EX.SEXO_ID Which table is EX?

  • @Luissouza It was meant to be HEY, was a typo, is already adjusted

  • @Paulor.F.Amorim The total number of students is appearing an unknown number.

  • @Luissouza was actually wrong was to be used the COUNT instead of SUM, I’ve already made the adjustment

  • @Paulor.F.Amorim I tested here and returns the total number of students, the total of male and female students, the total by code but, my problem is as follows. I need the male and female totals discounting the codes. Example: 10 female sex but, 2 has code 6 so the result should be 8. I have the total code but, I do not know if it is male or female

Show 2 more comments

2

Let me give you an example of how to build this using a select of multiple selects, but you could also put the results in a temporary table if you wanted to:

select descricao, valor from 
(
    select 'TOTAL ESTUDANTES' descricao, count(*) valor from ESTUDANTES_IDENTIFICACAO
    union
    select 'SEXO MASCULINO' descricao,  count(*) valor from ESTUDANTES_IDENTIFICACAO where sexo_id=1
    union
    select 'SEXO FEMININO' descricao,  count(*) valor from ESTUDANTES_IDENTIFICACAO where sexo_id=2
    union
    select 'SITUAÇÃO FINAL 3' descricao,  count(*) valor from TURMAS_HAS_ESTUDANTES where situacaco_final-3
    -- e faz os outros union com selects baseados nos selects acima, um para cada total que você precisa
) valores

Browser other questions tagged mysql

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