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.


more ▼

asked Jul 11, 2011 at 05:52 AM in Default

avatar image

23 2 2 5

Can you give us some sample data please.

Jul 11, 2011 at 06:13 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.


more ▼

answered Jul 11, 2011 at 07:31 AM

avatar image

2.8k 56 65 71

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jul 12, 2011 at 12:50 AM

avatar image

23 2 2 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 11, 2011 at 05:52 AM

Seen: 1358 times

Last Updated: Jul 11, 2011 at 05:52 AM

Copyright 2018 Redgate Software. Privacy Policy