Get average monthly BALANCE

[aznabeel]

on the below data i am trying to get average monthly BALANCE, based on the following query ....but i only getting all recods with actual balace and not month wise average balance of the customers......

can any body guide me...........................

-----------------------------------
SELECT [customer profit].[Customer Number], AVG ([customer profit].[VAL_BAL]) AS average, Month ([customer profit].[Balance Date])
FROM [customer profit]


GROUP BY [customer profit].[customer Number], [customer profit].[VAL_BAL], [customer profit].[Balance Date];
--------------------------------------
Data

Customer Number	Balance Date	VAL_BAL	CCY
40001	2/1/2014	1724.68	PKR
10001	2/2/2014	1724.68	PKR
40001	2/3/2014	401724.68	PKR
30001	2/4/2014	446724.68	PKR
40001	2/5/2014	446724.68	PKR
40001	2/6/2014	21724.68	PKR
10001	2/7/2014	1724.68	PKR
40001	2/8/2014	1724.68	PKR
20001	2/9/2014	1724.68	PKR
40001	2/10/2014	1724.68	PKR
20001	2/11/2014	1724.68	PKR
40001	2/12/2014	1724.68	PKR
30001	2/13/2014	1724.68	PKR
40001	2/14/2014	1724.68	PKR
40001	2/15/2014	1724.68	PKR
30001	2/16/2014	1724.68	PKR
40001	2/17/2014	1724.68	PKR
40001	2/18/2014	201724.68	PKR
10001	2/19/2014	186724.68	PKR
40001	2/20/2014	186724.68	PKR
30001	2/21/2014	86424.68	PKR
40001	2/22/2014	86424.68	PKR
20001	2/23/2014	86424.68	PKR
40001	2/24/2014	86424.68	PKR
40001	2/25/2014	86424.68	PKR
10001	2/26/2014	86424.68	PKR
40001	2/27/2014	6424.68	PKR
20001	2/28/2014	6424.68	PKR

[JoeM]

You need to group by the month, not by the whole date. You also do NOT want VAL_BAL find in your GROUP BY clause (just your Customer Number and Month).

If it does not let you do that all in one step, you may need to either use two queries or use a nested query.

[June7]

SELECT [customer profit].[Customer Number], AVG([customer profit].[VAL_BAL]) AS average, Format([customer profit].[Balance Date], "YYYYMO") AS YrMo
FROM [customer profit]
GROUP BY [customer profit].[customer Number], Format([customer profit].[Balance Date], "YYYYMO");

[ssanfu]

Uhmmm, shouldn't the format clause be

Format([customer profit].[Balance Date], "YYYYMM")
instead of
Format([customer profit].[Balance Date], "YYYYMO")???

[June7]

Ooooops! Thanks Steve.