I sent this out on last friday but I never actually saw it post to the list so maybe I
messed up the address. If this is showing up a second time, sorry.
I have a database where I want to count the number of days where a field gets to a
certain value. I am using two seperate queries to count the number of days that get
to that value. Right now I am using:
SELECT count(DISTINCT aqiValues.readingDate) as greenCount
FROM aqiRegions, aqiSites, aqiValues
WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND
aqiValues.site_name = aqiSites.siteNumber AND
aqiRegions.showOnSite = 1 AND
aqiSites.online = 1 AND
aqiRegions.aqiRegion = 'Twin Cities' AND
aqiValues.readingDate >= '2003/01/01' AND
aqiValues.aqiNumber between 0 and 51
Then I am also using:
SELECT count(DISTINCT aqiValues.readingDate) as yellowCount
FROM aqiRegions, aqiSites, aqiValues
WHERE aqiRegions.aqiRegionID = aqiSites.aqiRegionID AND
aqiValues.site_name = aqiSites.siteNumber AND
aqiRegions.showOnSite = 1 AND
aqiSites.online = 1 AND
aqiRegions.aqiRegion = ? AND
aqiValues.readingDate >= '2003/01/01' AND
aqiValues.aqiNumber between 51 and 100
My problem is I only want to count the day high value. So if it is 25 in the morning
and 52 in the afternoon I only want to count the 52 (the yellowcount query) to return
results. I have tried to work in the MAX(aqiNumber) into the query but I get a GROUP
error. I must be putting it in the wrong place. Maybe I need to somehow make this
only one query?
--ja
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
