Whenever you use the Max() function on a column while accessing other columns at the same time you need a GROUP BY statement so that the MAX() function knows how to "group" it's results.
I suggest you look into the manual for this. In fact it has a tutorial showing you options on how to do exactly what you want to do: <http://www.mysql.com/doc/en/Examples.html> In particular: <http://www.mysql.com/doc/en/example-Maximum-row.html> <http://www.mysql.com/doc/en/example-Maximum-column-group.html> <http://www.mysql.com/doc/en/example-Maximum-column-group-row.html> ck -- Cheap Domain Registration | Web Hosting | Email Packages | + more Fantastic prices -- Even better service. http://www.hosttohost.net [EMAIL PROTECTED] [EMAIL PROTECTED] on 10/20/03 6:52 AM wrote: > 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]
