| Author |
 Topic  |
|
|
rlopes
Starting Member
Portugal
6 Posts |
 Posted - 11/16/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/16/2012 : 13:32:39
|
SELECT SUM(Amount)/NULLIF(COUNT(DISTINCT DATEADD(mm,DATEDIFF(mm,0,[Date]),0)),0) AS Average
FROM Tbl |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/18/2012 : 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
Time is always against us
|
|
|
|
rlopes
Starting Member
Portugal
6 Posts |
Posted - 11/19/2012 : 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
Time is always against us
Whats the "a" in end for?  |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 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 |
|
|
| |
Topic |
|
|
|
AVG of the SUM of multiple MONTHS
