x

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
more ▼

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

Philleke gravatar image

Philleke
23 2 2 3

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.

Thanks,
Kannan
more ▼

answered Jul 11, 2011 at 07:31 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 53 57 62

(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

Philleke gravatar image

Philleke
23 2 2 3

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1951
x742
x376

asked: Jul 11, 2011 at 05:52 AM

Seen: 1119 times

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