asked
Select and average on same table
Hello I'm a newbie in sql and I received the following question: we need a Top 100/month on the column "Size" and the average on the same column "Size" but on all entries for that month. How can I do that within the same query? I also have to run this query for every month since the db is in use. How can I do that without changing the date range each time. Thnx
Hi , I guess we cant select both average (size) and top 100 size in the same query.But you could do something like this. --Average size of the month declare @average int SELECT @average = AVG(size) from dbo.Test where month = datename(month, getdate()) -- top 100 sizes of the month along with average select TOP 100 SIZE , @average from dbo.Test In order to execute the query every month. You need to create a sql server job with this query and schedule it every month. Hope this was useful. Thanks, Kannan
Hello Mrs_Fatherjack (and everyone else) This is what I have done so far with the result for a specific month: ---------- declare @dateinf char(10) declare @datesup char(10) set @dateinf = '2010-11-01' set @datesup = '2010-12-01' SELECT TOP 100 [content_size] AS 'Size' ,[object_id] AS 'Document ID' ,CONVERT(varchar(10), create_date, 103) AS 'Creation Date' FROM [DB1].[dbo].[Test] WHERE [Create_Date] >= @dateinf and [Create_Date] < @datesup Order by content_size DESC ---------- The goal is to get this result on monthly bases, by means of a SSIS package, together with the average of "content_size" for every entry in that column (not only for the TOP 100). @ Kannan: I will try your solution this afternoon and let you know about the result. Grtz and thnx to all for the reply
