AVG of the SUM of multiple MONTHS

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 2008 Forums

Transact-SQL (2008)

AVG of the SUM of multiple MONTHS

Author

Topic

rlopes
Starting Member

6 Posts

Posted - 2012-11-16 : 13:19:34

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 13:32:39

[code]SELECT SUM(Amount)/NULLIF(COUNT(DISTINCT DATEADD(mm,DATEDIFF(mm,0,[Date]),0)),0) AS Average
FROM Tbl[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-18 : 09:29:41

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
[spoiler]Time is always against us[/spoiler]

rlopes
Starting Member

6 Posts

Posted - 2012-11-19 : 05:44:35

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
[spoiler]Time is always against us[/spoiler]

Whats the "a" in end for?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 06:46:52

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources