GROUP BY - Display grouped result using WHERE as condition in same table - MySQL

Question

GROUP BY - Display grouped result using WHERE as condition in same table - MySQL

Navigation

#1 by (2 votes)

1

I'm trying to set up a query to fetch results in a table where I need to determine a condition for one column, group for another, and display a third.

The database is composed of 6 tables, 5 of which are queried individually, containing: id , entrada , saida , status , materia , exibir_entrada , exibir_saida compartilhada .

Below is the basic structure image of the tables:

Sofar,Igetaquerygroupingbystory.Ididthequerylikethis:

$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 show the information I need:

IeventriedtomakeaSELECTCOMPOUND:

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 not display the record of the entrada column.

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

The remaining values will be stored in a table with the schedules of the day as in the image has the table 03_01_2017 . This is where I need to enter the information.

php mysql sql sql-select group-by

asked by anonymous 04.01.2017 / 00:08

1 answer

Barriers with threads - Output code excerpt How to apply DropDownListFor with bootstrap?

score 2 · Accepted Answer

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

In addition, the exibir_entrada column and the entrada column contain the same information, only displayed differently. Therefore only one of them is necessary. The same can be said for columns exibir_saida and saida . Also, the output is always half an hour longer than the input, so we can eliminate as well.

The status is also expendable, as it is enough to see if there is any discipline allocated on schedule.

So you would arrive in a table containing: id , id_sala , entrada , materia , compartilhada .

However, your template 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 in 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 ) into your primary key.

Let's 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 in a certain room (and therefore rebuilding 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 it will bring the time table of room 1.

Now, to solve the problem you have at hand, you can take advantage of the foreign key when using 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 by room and / or discipline, you can add WHERE and / or SELECT to AND h.id_sala = 1 %.