Let's suppose 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 times, each with the amounts 50, 60 and 100 (70 + 30), respectively.
To select the records, displaying the date, time and total quantity of each one just do:
select 'data', 'hora', sum('quantidade') as 'total'
from quantidades
group by 'data', 'hora';
In this way, the three registers 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 make the insertion of 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;
Defines variables with values to insert into the database. These values are likely to be defined with PHP. Something like:
SET @data = "{$data}", @hora = "{$hora}", @quantidade = {$qnt};
Next, the statement of insert default, however, is not followed by values , but a select . This so that the values to be entered will only be passed from select to insert if the select condition is true. In this case, the condition is that the sum of the current total, plus the quantity to be entered, must be less than or equal to 150. If it is, the values are passed to insert , being inserted into the database. Otherwise, if the sum between the current total plus the quantity being entered exceeds 150, the record is not made.
It is important to note that, since the current total is checked before inserting a new record, the date and time must already be previously registered in the bank, because if they do not exist, the select condition is not satisfied and the record will never be made.
So, for example, when you enter an amount 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 90 is being inserted, 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 enter the same quantity at the time 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;
The 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 50, or less, will be done 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;
Getting into 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 was entered or not by checking the number of rows affected by the insert statement, using the PDOStatement::rowCount . If you returned 1, the record was entered, but if 0, the record was not entered because the total exceeded 150.
