SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 AVG of the SUM of multiple MONTHS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rlopes
Starting Member

Portugal
6 Posts

Posted - 11/16/2012 :  13:19:34  Show Profile  Reply with Quote
tableA
id Date Amount
1 10/11/2012 100
2 10/10/2012 100
2 11/10/2012 100
3 10/09/2012 100
4 10/08/2012 100

SELECT AVG(Amount) as Average FROM tableA
Well this query returns me an AVG value off all records (100) but what i need its to know the AVG of the SUM of the MONTHS (125).

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:32:39  Show Profile  Reply with Quote
SELECT SUM(Amount)/NULLIF(COUNT(DISTINCT DATEADD(mm,DATEDIFF(mm,0,[Date]),0)),0) AS Average
FROM Tbl
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17661 Posts

Posted - 11/18/2012 :  09:29:41  Show Profile  Reply with Quote
Or a slightly longer code but probably easier to understand

select	avg(Amount)
from
(
	select	Date = dateadd(month, datediff(month, 0, Date), 0), Amount = sum(Amount)
	from	tableA
	group by dateadd(month, datediff(month, 0, Date), 0)
) a



KH
Time is always against us

Go to Top of Page

rlopes
Starting Member

Portugal
6 Posts

Posted - 11/19/2012 :  05:44:35  Show Profile  Reply with Quote
quote:
Originally posted by khtan

Or a slightly longer code but probably easier to understand

select	avg(Amount)
from
(
	select	Date = dateadd(month, datediff(month, 0, Date), 0), Amount = sum(Amount)
	from	tableA
	group by dateadd(month, datediff(month, 0, Date), 0)
) a



KH
Time is always against us





Whats the "a" in end for?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  06:46:52  Show Profile  Reply with Quote
It is an alias for the subquery. You can then refer to the columns of that virtual table using the alias (as I am doing in the avg(a.amount)), or even join other tables to this virtual table.

It may be more clear perhaps if you add the optional "AS" keyword.
select	avg(a.Amount)
from
(
	select	Date = dateadd(month, datediff(month, 0, Date), 0), Amount = sum(Amount)
	from	tableA
	group by dateadd(month, datediff(month, 0, Date), 0)
) AS a
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000