FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE Brine_Reports (
  id INT AUTO_INCREMENT KEY,
  t_stamp DATETIME,
  Tank VARCHAR(20),
  Water INT,
  Salt INT,
  SeaSalt INT,
  Sugar INT,
  Pass INT
);

INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 08:00:00', 'Tank 1', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 08:15:00', 'Tank 2', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 08:30:00', 'Tank 3', 15, 15, 15, 15, 0);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 09:00:00', 'Tank 1', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 09:15:00', 'Tank 2', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 09:30:00', 'Tank 3', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 10:00:00', 'Tank 1', 15, 15, 15, 15, 0);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 10:15:00', 'Tank 2', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 10:30:00', 'Tank 3', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 11:00:00', 'Tank 1', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 11:15:00', 'Tank 2', 15, 15, 15, 15, 1);
INSERT INTO Brine_Reports (t_stamp, Tank, Water, Salt, SeaSalt, Sugar, Pass) VALUES ('2024-04-19 11:30:00', 'Tank 3', 15, 15, 15, 15, 1);

-- QUERY database
SELECT 
	Tank, SUM(Water) AS Water, SUM(Salt) AS Salt, SUM(SeaSalt) AS SeaSalt, SUM(Sugar) AS Sugar, 
    SUM(Pass) AS Passes, COUNT(Pass) - SUM(Pass) AS Fails
FROM Brine_Reports
WHERE t_stamp BETWEEN '2024-04-19 08:00:00' AND '2024-04-19 20:00:00'
GROUP BY Tank

Tank	Water	Salt	SeaSalt	Sugar	Passes	Fails
Tank 1	60	60	60	60	3	1
Tank 2	60	60	60	60	4	0
Tank 3	60	60	60	60	3	1