Need help on a Query... - SQL Server Forums

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

SQL Server Development (2000)

Need help on a Query...

Author

Topic

robert135
Starting Member

5 Posts

Posted - 2005-04-27 : 00:08:39

I need a hand on a query in MS access....

Here is a sample dataset I will call table dummydata


date	    Rain_inches	flowersbloomed
3/25/1999	0.25	    2
7/5/1985	0.125	    0
4/3/1987	1.2	  -999
5/2/2001	0.65	  -999
6/12/2001	0.1	    5

I need a yearly average for each field that does not include the -999 values (I have to keep them in the database).

Here is the current flawed Query....


Select distinct  Format$([DummyData].[date],'yyyy') AS [date By Year], 
(Select Avg(rain_inches) from DummyData where rain_inches > -999) as [Avg_Rain_Inches], 
(Select Avg(FlowersBloomed) from DummyData where FlowersBloomed > -999) as [Avg_Flowers_Bloomed]
from DummyData
group by Format$([DummyData].[date],'yyyy')

and Erroneous Data returned is


date By Year	Avg_Rain_Inches	Avg_Flowers_Bloomed
1985	0.465	2.33333333333333
1987	0.465	2.33333333333333
1999	0.465	2.33333333333333
2001	0.465	2.33333333333333

Any Ideas on what I need to do to seperate each into a year by year grouping of only that years average?

Thanks

robert135
Starting Member

5 Posts

Posted - 2005-04-27 : 00:18:35

I have no idea why when your at work you cannot figure something out... but when you ask the question on the net the answer always comes to you straight away......

For the curious...


Select distinct  Format$([z].[date],'yyyy') AS [date By Year], 
(Select Avg(rain_inches) from DummyData  x where rain_inches > -999  and Format$([x].[date],'yyyy')  = Format$([z].[date],'yyyy') ) as [Avg_Rain_Inches], 
(Select Avg(FlowersBloomed) from DummyData y where FlowersBloomed > -999 and Format$([y].[date],'yyyy')  = Format$([z].[date],'yyyy')) as [Avg_Flowers_Bloomed]
from DummyData z
group by Format$([z].[date],'yyyy')

robert135
Starting Member

5 Posts

Posted - 2005-04-27 : 00:22:16

and the data returned...


date By Year	Avg_Rain_Inches	   Avg_Flowers_Bloomed
1985	             0.125	          0
1987	             1.2	
1999	             0.25	          2
2001	             0.375	          5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources