Select Sum in table with PHP/Mysql

Question

I have a table with the following fields: date, time and quantity. And I need to make a select sum of it.

To better understand, I have a form where I insert a date, after I insert, I will make a query in the bank and in it I need to return the information of how many 'Qtd' has at a certain time of the chosen date.

Example: I need to add the whole 'Qtd' field on the date 2017-06-07 at 11-00, no longer allow entering values at that time if it exceeds 150 (integer).

This picture illustrates well what I need to do:

I’m using PHP/mysql, but I’m having trouble coming up with this logic. Does Select Sum do this kind of operation? What’s the best way to make that rule?

Can someone help me?

Answer 1

To make the check:

$select = "SELECT hora,COALESCE(SUM(qtd), 0) FROM table WHERE data='2017-06-07' GROUP BY hora"; //No caso coloquei a hora e data que você utilizou no seu exemplo, mas teria que ser as váriaveis que você pegou do formulário.
$comando = mysqli_query($connect, $select); //$connect é o endereço do servidor.

$count = mysqli_num_rows($comando);
if ($count > 0)
{
    while ($row=mysqli_fetch_row($comando))
    {
        printf ('Hora: %s Quantidade: %s \n', $row[0], $row[1]); //Só para fazer o teste, não necessário.
        if ($row[1] > 150)
        {
            echo "Indisponível.";
        }
        else
        {
            echo "Boa disponibilidade.";
        }
    }
}
else
{
    echo "Boa disponibilidade.";
}

To perform the update:

$select = "SELECT SUM(qtd) FROM table WHERE (data='2017-06-07' AND hora='11-00') GROUP BY hora";
$comando = mysqli_query($connect, $select);
$row = mysqli_fetch_row($comando);
if ($row[0] + $quantidade > 150)
{
    echo "Não existem tantas vagas.";
}
else
{
    $update = "UPDATE table SET qtd=qtd + '$quantidade' WHERE data='2017-06-07' AND hora='11-00'";
    mysqli_query($connect, $update);
}

Answer 2

Let’s assume that your table has the following structure:

create table quantidades (
    `id` int not null auto_increment,
    `data` date,
    `hora` time,
    `quantidade` int,
    primary key (id)
);

Considering some values in the table:

insert into quantidades 
values (default, "2017-06-06", "13:00:00", 50),
       (default, "2017-06-06", "14:00:00", 60),
       (default, "2017-06-06", "15:00:00", 70),
       (default, "2017-06-06", "15:00:00", 30);

That is, the system has three registered schedules, each with quantities 50, 60 and 100 (70+30), respectively.

To select the records, displaying the date, time and total amount of each one just do:

select `data`, `hora`, sum(`quantidade`) as `total` 
from quantidades 
group by `data`, `hora`;

In this way, the three records will be returned:

data       | hora     | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 50
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 100

To insert new values, ensuring that the total does not exceed 150, just do:

SET @data = "2017-06-06", @hora = "13:00:00", @quantidade = 60;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade 
    where (
        select sum(`quantidade`) 
        from quantidades
        where `data` = @data and `hora` = @hora
    ) + @quantidade  <= 150;

The first line:

SET @data = "2017-06-06", @hora = "13:00:00", @quantidade = 60;

Define the variables with the values to be entered into the database. These values will probably be set with PHP. Something like:

SET @data = "{$data}", @hora = "{$hora}", @quantidade = {$qnt};

After, the instruction of insert but not followed by values, but of a select. This so that the values to be entered will only be passed from the select to the insert if the condition of select is true. In this case, the condition is that the sum of the current total plus the quantity to be inserted must be less than or equal to 150. If it is, the values are passed to the insert, being entered into the database. Otherwise, if the sum between the current total and the quantity to be inserted exceeds 150, the record is not made.

It is important to note that, as the current total is verified before entering a new record, the date and time must already be previously registered in the bank, because if they do not exist, the condition of the select is not satisfied and registration will never be made.

So for example when inserting a quantity of 90 in 2017-06-06, 13:00:00

SET @data = "2017-06-06", @hora = "13:00:00", @quantidade = 90;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade 
    where (
        select sum(`quantidade`) 
        from quantidades
        where `data` = @data and `hora` = @hora
    ) + @quantidade  <= 150;

The registration will be done, because at this time the total amount in the bank is 50 and is being inserted 90, totaling 140, which does not exceed 150.

data       | hora     | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 140
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 100

However, if you try to insert the same amount at 15:00:00

SET @data = "2017-06-06", @hora = "15:00:00", @quantidade = 90;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade 
    where (
        select sum(`quantidade`) 
        from quantidades
        where `data` = @data and `hora` = @hora
    ) + @quantidade  <= 150;

Registration will not be done, because at this time there is already a total of 100 and 100+90 exceeds the limit of 150.

data       | hora     | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 140
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 100

A record of a quantity of 50, or less, will be made as expected:

SET @data = "2017-06-06", @hora = "15:00:00", @quantidade = 50;
insert into quantidades (`data`, `hora`, `quantidade`)
select @data, @hora, @quantidade 
    where (
        select sum(`quantidade`) 
        from quantidades
        where `data` = @data and `hora` = @hora
    ) + @quantidade  <= 150;

Staying on the table:

data       | hora     | total
-----------+----------+--------
2017-06-06 | 13:00:00 | 140
2017-06-06 | 14:00:00 | 60
2017-06-06 | 15:00:00 | 150

If you are using PDO, you can check whether the record has been entered or not by checking the number of lines affected by the instruction insert, through the method PDOStatement::rowCount. If return 1, the record was entered, but if 0, the record was not entered because the total exceeded 150.

Answer 3

Fiddle: http://sqlfiddle.com/#! 9/c75434/1

Data creation

CREATE TABLE registro (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `data` date,
    `hora` time,
    `quantidade` int
);

INSERT INTO registro   
VALUES (null, "2017-06-06", "13:00:00", 20),
       (null, "2017-06-06", "14:00:00", 30),
       (null, "2017-06-06", "15:00:00", 90),
       (null, "2017-06-06", "15:00:00", 80),
       (null, "2017-06-05", "15:00:00", 100);

Select grouped by date and time:

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro GROUP BY data, hora_minuto

Select grouped by specific date:

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro WHERE data = '2017-06-06' GROUP BY data, hora_minuto

Select by specific date and time

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro WHERE data = '2017-06-06' GROUP BY data, hora_minuto HAVING hora_minuto = '15:00'

Want to know which time has the most quantities (regardless of the day) ?

SELECT SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade FROM registro GROUP BY hora_minuto ORDER BY soma_quantidade DESC

Want to put the message on the return of the select ? (do not recommend)

SELECT data, SUBSTRING(hora, 1, 5) AS hora_minuto, SUM(quantidade) AS soma_quantidade, IF(SUM(quantidade) > 150, 'Indisponível', 'Boa disponibilidade') AS descricao FROM registro GROUP BY data, hora_minuto