IM no expert in writing sql statemsnt but ive finally got to one point that in needed to get but now i must do summarys on an existing query ive created:

The Following sql statement:

____________________________________________________________

select

DATABASEST storm_num,

max(maxwind_kt) max_wind_speed,

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

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

(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,

(SELECT top 1 avg(systemspee) FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST and lastpositi = 0 ) avg_trans_speed

from NHCBestTracks1851To2001$ s where

RECORDNUMB in (21085,5613,26836,16335,22280,33919,5951,2448,31919,12546,30243,28937,17161,22281,3198,21116,5952,12547,31596,25226,15828,7797,5119,32980,21084,13165,5612,11767,25816,8138,5120) and

( DY between 1 and 31 AND MON between 1 and 12 AND YR between 1850 and 2020 )

group by DATABASEST
____________________________________________________________


produces this output:

storm_num max_wind_speed duration_days storm_name PRESSURE_M storm_date hour_utc ss_scale year_storm_num comment avg_trans_speed
154 30 0 Not Named  0 4/9/1874 0 TS  4 * 5.68695652173913
183 70 0 Not Named  0 3/10/1878 0 SS4 7 * 9.30677966101695
250 75 1 Not Named  0 20/8/1887 0 SS3 5 * 13.8583333333333
271 85 0 Not Named  0 23/11/1888 6 SS2 9 * 9.39677419354839
280 50 1 Not Named  0 4/10/1889 18 TS  9 * 16.6846153846154
332 35 0 Not Named  0 3/9/1897 6 SS2 1 * 15.0976190476191
342 40 0 Not Named  0 20/9/1898 12 TS  6 * 7.74545454545455
450 70 0 Not Named  0 5/7/1916 6 SS3 1 * 6.49777777777778
479 85 1 Not Named  0 20/6/1921 6 SS2 1 * 8.02826086956522
499 70 0 Not Named  0 13/9/1924 18 SS1 4 * 17.2962962962963
588 45 0 Not Named  892 3/9/1935 0 SS5 2 * 14.4204081632653
609 60 0 Not Named  0 1/8/1937 0 TS  1 * 16.4176470588235
641 70 0 Not Named  0 19/9/1941 12 SS1 3 * 8.63666666666666
787 65 8 Ione  0 18/9/1955 6 SS3 9 * 15.1090909090909
832 130 0 Donna  0 4/9/1960 6 SS5 5 * 15.7622950819672
916 50 0 Subtrop 1  998 1/10/1969 0 TS  12 S 8.55555555555556
938 35 0 Edith  943 9/9/1971 18 SS5 6 * 11.49
972 60 0 Amy  984 1/7/1975 0 TS  1 * 11.7133333333333
1047 20 0 Alicia  963 18/8/1983 6 SS3 1 * 8.05217391304348
1095 30 0 Gilbert  888 14/9/1988 0 SS5 8 * 16.0288888888889
1136 55 0 Charley  965 24/9/1992 18 SS2 4 * 9.61034482758621
1148 20 0 Alberto  993 3/7/1994 12 TS  1 * 5.89
1178 55 0 Edouard  933 25/8/1996 6 SS4 5 * 12.5138888888889
1205 75 0 Karl  970 27/9/1998 0 SS2 11 * 20.596

___________________________________________________________

I dont know how clear that is but basically, the most important thing comming out here is the max_wind_speed.

I need to another report from this query that groups the wind speeds in categories.

the result will be one row that outputs the counts for each category

slow = 0 - 10
med = 11 - 30
medfast = 20 - 40
fast = 40 - 70
etc....


so like

slow med medfast fast ....
 0    2     4     12  .....


if i was to take the result here and put insert it into a temp table it wouldnt be too hard to do. I could just run a query on the temp table. but im using this for multi user web application and would rather, if its possible, put it all in one statement. or at least figure out what the best thing to do is.

I dont know if its possible to get it all in one statement ..?

(IM using sql server and asp.net)

thanks,

dave