Selecting non aggregate values within an aggregate sql statement
Selecting non aggregate values within an aggregate sql statement
(OP)
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
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
RE: Selecting non aggregate values within an aggregate sql statement
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
RE: Selecting non aggregate values within an aggregate sql statement
thanks
dave
RE: Selecting non aggregate values within an aggregate sql statement
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
RE: Selecting non aggregate values within an aggregate sql statement
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
RE: Selecting non aggregate values within an aggregate sql statement
BML