Selecting non aggregate values within an aggregate sql statement

Dave,

Try including the pressure, storm number, and other non-aggregate fields in your group by expression. If I understand you correctly, these values don't change among all the rows for a given storm, so including them in your group by shouldn't cause any problems.

Hope this helps!

BML

the storm data in the database are ordered by 6 hour tracks. so a storm can have multiple rows and at each 6 hour segment, pressure, yearlystormnum, date/time at strongest wind - can change with each interval. and the values i want are those that correspond to the touple where the strongest wind is for that storm.

thanks

dave

I think you need to use a nested query. Try something like this:

SELECT
A.databasesest, A.avgsystemspee, A.maxmaxwind_kt,
B.yearlystormnum, B.pressure

FROM
(SELECT databasesest, avg(systemspee) AS avgsystemspee,
max(maxwind_kt) AS maxmaxwind_kt
FROM storms
GROUP BY databasesest) A,
storms B

WHERE
A.databasesest = B.databasest
AND A.maxmaxwind_kt = B.maxwind_kt

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&quot 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

As far as I know you would have to use 2 queries if your WHERE clauses are different. So one query would calculate the average system speed where lastpositi <> 1, and the second query would calculate the max wind speed with no mention of lastpositi in the WHERE clause.

BML