I have a table of storm info that has multiple rows for each storm.

I need to do a group by storm number (databasest). For each storm, i need to select the max of the max wind speed max(maxwind_kt), the average system speed (avg(systemspee)),

I have to write an sql statement where i select the values from these aggregate functions and the storm number that ive grouped by but i also need the single (non aggregrate) values that correspond to the max wind speed for each storm. I dont know if its possible, or if there is some kind of trick to get single values that arent grouped by when selecting aggregate functions.

ex:

select databasesest, avg(systemspee), max(maxwind_kt), pressure, yearlystormnum from storms where databasest in (30,31,32,33) group by databasest.

of course this doesnt work.
pressure and yearlystormnum are values that i would like to get which correspond to the row that returned the max wind speed but i want a clean sql statement that can do this. or even a smart method that can help me do this. but i have no idea where to start on how to go about doing it??

Thanks,

Dave

Hi,

I think you need to write a piece of code to do what you want. You want to select upon multiple parameters and return multiple results (at least that's what I'm hearing).

If this is something you need to do often, it's probably better to create a new table, that contains these max, min, average results, and run a cron job once a day to maintain it, or as often as necessary. (It could be smart, such as check for new records, and only update those storms mentioned in the records posted since some date).


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

well that wouldnt totally (the go-between table idea) because the stats have to be calculate on the fly. This is because a person may specify a specify area (lats and longs) and all of this has to be figured out on the fly like that. So im guessing there is no way to get these single values along with the aggregate functions?
thanks,

dave

okay ive got the statment almost perfect


select

DATABASEST storm_num,

avg(systemspee) avg_trans_speed,

max(maxwind_kt) max_wind_speed,

datediff(dd, min(convert(datetime, [date],104)), (max(convert(datetime, [date],104)))) duration_days,

(SELECT TOP 1 PRESSURE_M FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) PRESSURE_M,

(SELECT TOP 1 [date] FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) storm_date,

(SELECT TOP 1 hour_utc FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) hour_utc,

(SELECT TOP 1 saffirsimp FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) ss_scale,

(SELECT TOP 1 yearlystor FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) year_storm_num,

(SELECT TOP 1 comment FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) comment

from NHCBestTracks1851To2001$ s

where lastpositi = 0 and DATABASEST in (30,31,32,33)

group by DATABASEST


-----

now you might notice that i have "where lastpositi = 0". This is because in this table lastpositi represents weather it is the last segment of the storm. and if it is then the translation speed is 0 so it should not be included in the avg(systemspee). however, the that tuple is usefull in calculating the max(windspeed_kt) (dont want to exclude it from this calculation) and also if the max windspeed is at this row (where the translation speed is 0 ie lastpositi = "1") then i need all the corresponding values like pressure, storm_date, time_utm .... that go with that max speed.
So is there a way in this same statment to ensure that the row with lastpositi = 1 gets excluded from the avg(systemspee) but is included in the max(maxwind_kt).

thanks,

dave