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.
Author |
Topic |
rlopes
Starting Member
6 Posts |
Posted - 2012-11-16 : 13:19:34
|
tableAid Date Amount1 10/11/2012 1002 10/10/2012 1002 11/10/2012 1003 10/09/2012 1004 10/08/2012 100SELECT AVG(Amount) as Average FROM tableAWell 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 AverageFROM 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 understandselect 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 understandselect 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 |
|
|
|
|
|
|
|