aggregate functions with single selects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

aggregate functions with single selects

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2003-04-28 : 10:24:46

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

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-04-28 : 10:31:51

Correlated subquery?


select databasesest, avg(systemspee), max(maxwind_kt),(SELECT TOP 1 pressure FROM storms where storms.databasest = s.databasest Order by maxwind_kt desc ) pressure
from storms s 
where databasest in (30,31,32,33) group by databasest.

same way you can select other 'single' values

Edited by - Peter Dutch on 04/28/2003 10:32:34

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-28 : 10:35:33

1) You dont need to post your question in two places to get it answered.

2) Please post the necessary part of the table structure...

However, I am willing to venture a guess:

select s.databasesest, a.AvgSpee, a.MaxSpee, pressure, yearlystormnum from storms s INNER JOIN
(select databasesest, avg(systemspee) AS AvgSpee, max(maxwind_kt) AS MaxWind from storms where databasest in (30,31,32,33) group by databasest) a
ON a.databasesest = s.databasesest

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-28 : 10:38:38

Not sure EXACTLY what you mean (sample data and expected results would help) but:

select a.databaseset, a.AvgSystemSpee, a.MaxWindKt,
pressure, yearlystormnum
FROM
(
select databasesest,
avg(systemspee) AvgSystemSpee,
max(maxwind_kt) MaxWindKt
from
storms
where databasest in (30,31,32,33)
group by databasest.
) A
INNER JOIN
storms
ON
a.databaseset = storms.databaseset

Note that the first 3 values are the aggregate value for each databaseset, and the last 2 values are individual row values.

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-28 : 10:59:09

Read my post in your other thread ...

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-28 : 11:18:48

Jay .... great minds think alike I guess ...
(your solution is exactly the same as mine

)

- Jeff

Edited by - jsmith8858 on 04/28/2003 11:19:22

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-29 : 03:48:13

quote:

Jay .... great minds think alike I guess ...
(your solution is exactly the same as mine )

- Jeff

Edited by - jsmith8858 on 04/28/2003 11:19:22

Huh? what about me? You missed me by three whole minutes!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-29 : 07:53:47

D'oh! I didn't even see yours! again, exactly the same !

I guess you get the credit -- yours was first!

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-29 : 09:06:40

If you were truly as great a mind as I, you would have read

quote:
which correspond to the row that returned the max wind speed

and the ON clause of you join would look more like mine

Jay White
{0}

dbenoit64
Starting Member

36 Posts

Posted - 2003-04-30 : 12:39:45

Actually,
the correlated subquery by Peter Dutch does exactly what I need aparently. I havent tried the others you guys have written but i appreciate it. Maybe they work the same way but actually I noticed that Jay mentioned that if there are other max wind + average systemspeed pairs that his method would work.

Just ordering desc and grouping by storm number then doing corelated selects for each single corresponding element seems to do the trick.

If Im wrong here please let me know.

thanks

dave

dbenoit64
Starting Member

36 Posts

Posted - 2003-04-30 : 15:16:04

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources