I am creating a report that is displaying the total value of ingredients added within a selected time frame for separate tanks. I am currently storing all tank ingredient data in the same table and have separate queries filtering the values based on the tank identifier. Doing it this way, I have to create separate tables with the different data source, and the design is sloppy. This works, but I wonder if there is a way to use only one table, or alternatives. Thanks.
If you looking for a report of the form,
| 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 |
then your query will be of the form,
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
Try it out at SQL Fiddle.
Thanks, this is what I'm looking for.