sum Aggregate function with date lengths<> problems

[opie_18]

Is it true that with this query and the results below not returning just one row would be becuase my date column lenghts are different lengths, i.e. some are 8 char's long and some dates are 9 char's long?????

SELECT DISTINCT [Budget].[Hotel], "Janurary" AS DrptMonth, [Budget].[Account], [Budget].[Description], [Budget].[Amount], Sum([Budget].[Amount]) AS SumOfAmount
FROM Budget
WHERE ((([Budget].[Account])="10-41220")) And month([budget].[drptdate])=1 And [budget].[hotel]="CQSC"
GROUP BY  [Budget].[Hotel], [Budget].[Account], [Budget].[Description], [Budget].[Amount];

Hotel	DrptMonth	Account	Description	Amount	SumOfAmount
CQSC	Janurary	10-41220	Business Travel Club	-820	-820
CQSC	Janurary	10-41220	Business Travel Club	-492	-492
CQSC	Janurary	10-41220	Business Travel Club	-410	-820
CQSC	Janurary	10-41220	Business Travel Club	-246	-246
CQSC	Janurary	10-41220	Business Travel Club	-164	-1312
CQSC	Janurary	10-41220	Business Travel Club	-82	-1476

[brucevde]

Nope. The problem is you are grouping using the Amount column.

Remove the [Budget].[Amount] column from the selection list and the group by clause and you should only get 1 row.

[opie_18]

I don't see what I am doing wrong... I want 1 row sumed up for the month, not everyday sumed up...

Please help

SELECT Hotel, DrptDate, Account, Description, Sum(Amount) as Total
FROM Budget where hotel = 'ABVA' and month(drptdate) = 1 and year(drptdate) = '2005' and account = '199-41200'
Group by Hotel, DrptDate, Account, Description;

Hotel	DrptDate	Account	Description	Total
ABVA	1/1/2005	199-41200	Rooms Rented - Rack/Corp Transient	8
ABVA	1/2/2005	199-41200	Rooms Rented - Rack/Corp Transient	8
ABVA	1/3/2005	199-41200	Rooms Rented - Rack/Corp Transient	23
ABVA	1/4/2005	199-41200	Rooms Rented - Rack/Corp Transient	23
ABVA	1/5/2005	199-41200	Rooms Rented - Rack/Corp Transient	25
ABVA	1/6/2005	199-41200	Rooms Rented - Rack/Corp Transient	9
ABVA	1/7/2005	199-41200	Rooms Rented - Rack/Corp Transient	9
ABVA	1/8/2005	199-41200	Rooms Rented - Rack/Corp Transient	19
ABVA	1/9/2005	199-41200	Rooms Rented - Rack/Corp Transient	4
ABVA	1/10/2005	199-41200	Rooms Rented - Rack/Corp Transient	28
ABVA	1/11/2005	199-41200	Rooms Rented - Rack/Corp Transient	18
ABVA	1/12/2005	199-41200	Rooms Rented - Rack/Corp Transient	22
ABVA	1/13/2005	199-41200	Rooms Rented - Rack/Corp Transient	12
ABVA	1/14/2005	199-41200	Rooms Rented - Rack/Corp Transient	7
ABVA	1/15/2005	199-41200	Rooms Rented - Rack/Corp Transient	3
ABVA	1/16/2005	199-41200	Rooms Rented - Rack/Corp Transient	6
ABVA	1/17/2005	199-41200	Rooms Rented - Rack/Corp Transient	23
ABVA	1/18/2005	199-41200	Rooms Rented - Rack/Corp Transient	35
ABVA	1/19/2005	199-41200	Rooms Rented - Rack/Corp Transient	17
ABVA	1/20/2005	199-41200	Rooms Rented - Rack/Corp Transient	21
ABVA	1/21/2005	199-41200	Rooms Rented - Rack/Corp Transient	19
ABVA	1/22/2005	199-41200	Rooms Rented - Rack/Corp Transient	3
ABVA	1/23/2005	199-41200	Rooms Rented - Rack/Corp Transient	7
ABVA	1/24/2005	199-41200	Rooms Rented - Rack/Corp Transient	8
ABVA	1/25/2005	199-41200	Rooms Rented - Rack/Corp Transient	19
ABVA	1/26/2005	199-41200	Rooms Rented - Rack/Corp Transient	17
ABVA	1/27/2005	199-41200	Rooms Rented - Rack/Corp Transient	12
ABVA	1/28/2005	199-41200	Rooms Rented - Rack/Corp Transient	11
ABVA	1/29/2005	199-41200	Rooms Rented - Rack/Corp Transient	5
ABVA	1/30/2005	199-41200	Rooms Rented - Rack/Corp Transient	8
ABVA	1/31/2005	199-41200	Rooms Rented - Rack/Corp Transient	16

[brucevde]

To Group By an expression include the expression in both the Select List and Group By clause

SELECT Hotel, Month(DrptDate) , Account, Description, Sum(Amount) as Total
FROM Budget 
Where hotel = 'ABVA' and month(drptdate) = 1 and year(drptdate) = '2005' and account = '199-41200'
Group by Hotel, Month(DrptDate) , Account, Description;