GROUP BY - Display grouped result using WHERE as a condition in the same table - Mysql

Question

I am trying to assemble a query to search results in a table where I need to determine a condition for one column, group by another and Display a third.

The database consists of 6 Tables, 5 of which are consulted individually, containing: id, entrada, saida, status, materia, exibir_entrada, exibir_saida, compartilhada.

Below is the image of the base structure of the tables:

So far, I can get a query grouping by matter. I did the query like this:

$QueryBuscarAgruparMaterias = "SELECT count(*),materia FROM $NomeSala GROUP BY materia HAVING materia = 'Geografia'";
$ExeQrBuscarAgruparMaterias = mysql_query($QueryBuscarAgruparMaterias);
  while ($MateriasAgrupadas = mysql_fetch_assoc($ExeQrBuscarAgruparMaterias)){
  ?>
    <option value="<?php echo $MateriasAgrupadas['entrada'] ?>">
    <?php echo $MateriasAgrupadas['entrada'] ?>
    </option>
  <?php
}

But the result does not present the information I need:

I even tried to make a SELECT COMPOSITE:

select entrada, count(materia) from 
(
    select*from
    (
        select * from $NomeSala WHERE compartilhada = 1 AND materia = 'Geografia'
    )
)as t GROUP BY materia;

Even so, I can’t display the column logs entrada.

The data of this query will be used to insert a new record in another table, thus generating a schedule of classes, both shared and private.

The other values will be stored in a table with the schedules of the day as shown in the picture 03_01_2017. That’s where I need to enter the information.

Answer 1

First, creating a table per room is silly. Think about it, you could add a column containing the number of the room and with that would be enough a single table.

In addition, the column exibir_entrada and the column entrada contains the same information, only displayed in a different way. Therefore only one of them is required. The same can be said for columns exibir_saida and saida. Also, the exit is always half an hour longer than the input, so we can also eliminate.

The status is also expendable because just see if there is any discipline allocated on the schedule.

Therefore, you would arrive at a table containing: id, id_sala, entrada, materia, compartilhada.

However, your model is not normalized. Part of your difficulty must be coming from this, as there is a lot of redundant and duplicate data. See more about normalization here. Discipline and room information should be on separate tables. I strongly suspect that there are other normalizations that could be applied, but would have to see more deeply what you are trying to do.

In addition, we can see that for each room, there should be one and only one tuple for each time. So, we can transform the set (id_sala, entrada) in your primary key.

We will restructure your database as follows:

CREATE TABLE sala (
    id_sala int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id_sala)
) ENGINE = INNODB;

CREATE TABLE disciplina (
    nome_disciplina VARCHAR(30) NOT NULL,
    PRIMARY KEY (nome_disciplina)
) ENGINE = INNODB;

CREATE TABLE horario (
    entrada TIME NOT NULL,
    id_sala int(11) UNSIGNED NOT NULL,
    nome_disciplina VARCHAR(30) NULL,
    compartilhada int(11) NOT NULL,
    PRIMARY KEY (id_sala, entrada),
    FOREIGN KEY (nome_disciplina) REFERENCES disciplina(nome_disciplina),
    FOREIGN KEY (id_sala) REFERENCES sala(id_sala),
) ENGINE = INNODB;

With this, you can know all the information of a given room (and therefore reconstructing your original data) with the following query:

SELECT entrada,
       (entrada + INTERVAL 30 MINUTES) AS saida,
       IF(ISNULL(nome_disciplina), 'Livre', 'Ocupada') AS status,
       nome_disciplina,
       compartilhada
FROM horario
WHERE id_sala = 1;

In the case of the above query, obviously, will bring the schedule table of room 1.

Now, to solve the problem you have at hand, you can take advantage of the foreign key when using the GROUP BY:

SELECT m.id_sala,
       m.nome_disciplina,
       MIN(m.entrada),
       MAX(m.saida)
FROM (
    SELECT h.entrada AS entrada,
           (h.entrada + INTERVAL 30 MINUTES) AS saida,
           h.nome_disciplina AS nome_disciplina,
           h.id_sala AS id_sala
    FROM horario h
    INNER JOIN disciplina d ON h.nome_disciplina = d.nome_disciplina
    WHERE h.compartilhada = 1
) m
GROUP BY m.id_sala, m.nome_disciplina;

If you really want to restrict the result per room and/or per discipline, you can add to WHERE of SELECT internal a AND h.id_sala = 1 and/or a AND h.nome_disciplina = 'Geografia'.