Hi
I was wondering if there is a way to exclude zeros in the report calculations IE "MIN, AVG" on the php side of the program. Right now I have three tables with data and if I use a WHERE clause to remove the ZERO'S on the query side I end up with missing row's. I have tried many types of joins and still end up with missing rows of data that are needed for the report. I am using the date and time columns to link the tables together, so when I run a query to remove the zeros in each column the date and time columns don't match up
So if i need to remove the row 12/07 00:30 in table one it will not match in table two.
I am using mysql 5 with php 5 and apache 2 running on a windows xp sp2 machine
The ver of phprunner is 4.2b 334
Table One - Water In SYS
col1 col2 col3 col4 col5 col6
Date Time Op Code Flow gal Turb NTU pH
12/07 :00 50 12 160.3 6.94
12/07 :15 50 8104 161.2 6.92
12/07 :30 50 0 0 0
12/07 :45 50 0 0 0
12/07 1:00 50 0 158.0 6.90
12/07 1:15 50 0 163.4 6.93
Table Two - Water Out SYS
col1 col2 col3 col4 col5 col6 col7
Date Time Op Code Flow gal Flow Rate Turb NTU pH
12/07 :00 50 2376 446 8.2 6.96
12/07 :15 50 1016 581 9.4 6.99
12/07 :30 50 5729 557 8.9 6.97
12/07 :45 50 1517 557 9.3 6.96
12/07 1:00 50 0 0 8.3 6.96
12/07 1:15 50 0 0 7.9 6.94
Table Three - Water Recirc SYS
col1 col2 col3 col4 col5 col6
Date Time Op Code Flow gal Turb NTU pH
12/07 :00 50 6176 8.1 6.98
12/07 :15 50 7378 12.2 6.99
12/07 :30 50 6572 17.1 6.96
12/07 :45 50 0 0 6.95
12/07 1:00 50 6959 38.2 6.98
12/07 1:15 50 815 9.9 6.94
Here last query i was messing with
SELECT
slc500_in.sysid,
slc500_in.`date`,
COUNT(slc500_in.`time`)/4 AS ` Total OP Hrs`,
SUM(slc500_in.flow_in_gal) AS `Gallons Treated`,
SUM(slc500_out.flow_out_gal) AS `Gallons Discharged`,
SUM(slc500_rec.flow_rec_gal) AS `Gallons Recirc`,
MIN(slc500_out.tur_out_ntu) AS `NTU Min`,
MAX(slc500_out.tur_out_ntu) AS `NTU Max`,
(SUM(slc500_out.flow_out_gal * slc500_out.tur_out_ntu)/ SUM(slc500_out.flow_out_gal)) AS `Flow weighted average NTU`,
MIN(slc500_out.ph_out) AS `pH MIN`,
MAX(slc500_out.ph_out) AS `pH MAX`
FROM
slc500_in
inner Join slc500_out ON slc500_out.sysid = slc500_in.sysid AND slc500_out.`date` = slc500_in.`date` AND slc500_out.`time`= slc500_in.`time`
inner Join slc500_rec ON slc500_rec.sysid = slc500_in.sysid AND slc500_rec.`date` = slc500_in.`date` AND slc500_rec.`time` = slc500_in.`time`
GROUP BY
date(`slc500_in`.`date`)
Thank you for any ideas or help
Travis L Wilson
