Selecting non aggregate values within an aggregate sql statement

[dbenoit64]

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

 

[BML]

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

 

[dbenoit64]

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

 

[BML]

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

 

[dbenoit64]

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

 

[BML]

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